One Server Record To Another Server

Kailash...

Participant

206 Points

23 Posts

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

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

Share:

Answers

Rahul M...

Teacher

4822 Points

23 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:

Transact-SQL

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.

Transact-SQL

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.

Transact-SQL

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.

Transact-SQL

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.

Posted On: 31-Jan-2017 00:32