Use linked server:
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.
For more information what is linked server click here and How to create linked server click here
Following steps you can use:
To create a linked server to another instance of SQL Server using Transact-SQL
In Query Editor, enter the following Transact-SQL command to link to an instance of SQL Server named SRVR002\ACCTG:
@server = N'SRVR002\ACCTG',
@srvproduct=N'SQL Server' ;
Execute the following code to configure the linked server to use the domain credentials of the login that is using the linked server.
@rmtsrvname = N'SRVR002\ACCTG',
@locallogin = NULL ,
@useself = N'True' ;
Follow Up: Steps to take after you create a linked server
To test the linked server
Execute the following code to test the connection to the linked server. This example the returns the names of the databases on the linked server.
SELECT name FROM [SRVR002\ACCTG].master.sys.databases ;
Writing a query that joins tables from a linked server
Use four-part names to refer to an object on a linked server. Execute the following code to return a list of all logins on the local server and their matching logins on the linked server.
SELECT local.name AS LocalLogins, linked.name AS LinkedLogins
FROM master.sys.server_principals AS local
LEFT JOIN [SRVR002\ACCTG].master.sys.server_principals AS linked
ON local.name = linked.name ;
When NULL is returned for the linked server login it indicates that the login does not exist on the linked server. These logins will not be able to use the linked server unless the linked server is configured to pass a different security context or the linked server accepts anonymous connections.