[sql] Selecting data from two different servers in SQL Server

You can do it using Linked Server.

Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle. Many types OLE DB data sources can be configured as linked servers, including Microsoft Access and Excel.

Linked servers offer the following advantages:

  • The ability to access data from outside of SQL Server.
  • The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
  • The ability to address diverse data sources similarly.

Read more about Linked Servers.

Follow these steps to create a Linked Server:

  1. Server Objects -> Linked Servers -> New Linked Server

  2. Provide Remote Server Name.

  3. Select Remote Server Type (SQL Server or Other).

  4. Select Security -> Be made using this security context and provide login and password of remote server.

  5. Click OK and you are done !!

Here is a simple tutorial for creating a linked server.

OR

You can add linked server using query.

Syntax:

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] 
     [ , [ @provider= ] 'provider_name' ]
     [ , [ @datasrc= ] 'data_source' ] 
     [ , [ @location= ] 'location' ] 
     [ , [ @provstr= ] 'provider_string' ] 
     [ , [ @catalog= ] 'catalog' ] 

Read more about sp_addlinkedserver.

You have to create linked server only once. After creating linked server, we can query it as follows:

select * from LinkedServerName.DatabaseName.OwnerName.TableName