Joining Tables from Databases on Different SQL Servers

In my previous article, I’ve explained the select query joining tables from different database on the same SQL Server. In this article, I’ll explain joining tables from databases on different SQL Servers.

There are 2 steps to join tables from different servers. The first step is to link the SQL Servers. The next and the last step is to join the tables using the select query having the server name as prefix for the table name.

1. Linking The SQL Servers

The first step is to link the SQL Servers (or instance)  using the system stored procedures sp_addlinkedserver and sp_addlinkedsrvlogin. The system stored procedure sp_addlinkedserver is used to link the remote server and sp_addlinkedsrvlogin is used to configure the linked remote server to use the local login credentials

A basic syntax for using sp_addlinkedserver and sp_addlinkedsrvlogin is:

sp_addlinkedserver @Server, @SrvProduct;

sp_addlinkedsrvlogin @RemoteServerName, @LocalLogin, @UseSelf;

For our example, I’m linking the secondary named instance of the SQL Server [MACWINDOWS\MSSQLSERVER2] with my primary or default instance [MACWINDOWS]. The sample code for creating the linked server will be like this:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver 
    @server = N'MACWINDOWS\MSSQLSERVER2', 
    @srvproduct=N'SQL Server' ;
GO

EXEC master.dbo.sp_addlinkedsrvlogin 
    @rmtsrvname = N'MACWINDOWS\MSSQLSERVER2', 
    @locallogin = NULL, 
    @useself = N'True' ;
GO

More details about linked servers and using  sp_addlinkedserver and sp_addlinkedsrvlogin is available here.

2. Joining Tables Between Linked SQL Servers

Once the remote server is linked, then you can join tables from the linked remote server with the local server by having the server name as prefix for the table name. here is the sample SQL script for joining tables from databases on different SQL Servers:

SELECT
	t1.SlNo,
	t2.FirstName,
	t2.LastName
FROM
	[MACWINDOWS\MSSQLSERVER2].[MtbDbTwo].[dbo].[TableTwo] as t2
	INNER JOIN [MtbDbOne].[dbo].[TableOne] as t1 on t1.SlNo = t2.SlNo;
GO
Joining Tables from Databases on Different SQL Servers

The sample queries mentioned in this article are tested and working good on SQL Server 2014 Developer Edition.

Related Articles


3 thoughts on “Joining Tables from Databases on Different SQL Servers”

  1. I know this is an old post but the four part naming method can cause some really big performance issues. It’s better to access remote data using the openquery method.

    Here is another article supporting my opinion:
    https:// maxteo.wordpress.com/2009/07/29/openquery-vs-4-part-name-query/

    Like Brent Ozar says, Mileage may vary so test both ways.

    Reply
  2. I got this error while trying to execute the store proc link server with login
    The server ‘abc\xyz’ does not exist. Use sp_helpserver to show available servers.

    Reply
    • Please see if the server name and the instance names are not misspelled, then try using sp_helpserver to confirm if the server is accessible.

      Reply

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.