One Server Record To Another Server

Kailash Singh
66 Points
28 Posts

How to Insert one table record to another table When both tables are in different database & server.?

Views: 9213
Total Answered: 1
Total Marked As Answer: 0
Posted On: 31-Jan-2017 00:14

Share:   fb twitter linkedin
Rahul Maurya
Rahul M...
1402 Points
236 Posts

Hi Kailash,

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:


USE [master]
EXEC master.dbo.sp_addlinkedserver
@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.


EXEC master.dbo.sp_addlinkedsrvlogin
@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 AS LocalLogins, AS LinkedLogins
FROM master.sys.server_principals AS local
LEFT JOIN [SRVR002\ACCTG].master.sys.server_principals AS linked
ON = ;

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.

Posted On: 31-Jan-2017 00:32
 Log In to Chat