How to write Sql Query using more than one Server

In some scenario we need to write query using more than one server like if we want to copy data from one server table to another or want to use join between two server tables then we need to user link server concept or you need to select data from two different servers.

There are some specific steps which we need to follow before write the query with two sql server database.

Step 1. Insert link server information into sys.server
By the help of following Stored Procedure you can insert information of your Link Sever.

execute sp_addlinkedserver [Server Name]

This Procedure will insert your server information into sys.servers

Step 2. You need to write you sql query with full server details like as follows

Example:

 

SqlServer1 Name- Test1
SqlServer1 Database- Database1
SqlServer1 Table- Table1
SqlServer2 Name- Test2
SqlServer2 Database- Database2
SqlServer2 Table- Table2

SELECT a.*,b.*
FROM [Test1].[ Database1].[dbo].[Table1] a,
[Test2].[ Database2].[dbo].[ Table2] b

Hope this will help. If you need anything in detail then just leave a comment.

Author: Ankur

Have worked primarily in the domain of Calling, CRM and direct advertisers services. My technological forte is Microsoft Technologies especially Dot Net (Visual Studio 2003, 2005, 2008, 2010 and 2012) and Microsoft SQL Server 2000,2005 and 2008 R2. My Area of Expertise is in C#. Net, VB.Net, MS-SQL Server, ASP. Net, Silverlight, HTML, XML, Crystal Report, Active Reports, Infragistics, Component Art, ComponeOne, Lead Tools etc.