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

Leave your thoughts...