SQL Server – Insert Data From Linked Server Table

In a situation where Servers are linked, it is possible to retrieve data from a table in one server to a table on the other server.
Following is the method to carry this out:

SELECT @@SERVERNAME

This will return the server’s name to which you are currently connected to.

SELECT * FROM [bngstagedb]. testDB.dbo.temp2 WITH (NoLock)

In the above statement ‘WITH’ clause is significant. If not used then you will receive the error ‘Remote table-valued function calls are not allowed.’. Hence, it is required for you to either remove NoLock clause in the statement or add ‘WITH’ clause to the NoLock.

Below statement will allow you to make a copy of the required table in the current server from the linked server:

SELECT * INTO temp2
FROM [bngstagedb].testDB.dbo.temp2 WITH (NoLock)

And the below statement will allow you to insert values in an existing table from the linked server:

INSERT INTO temp2
SELECT * FROM [bngstagedb].testDB.dbo.temp2 WITH (NoLock)