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)
Showing posts with label Copy Table From Linked Server. Show all posts
Showing posts with label Copy Table From Linked Server. Show all posts
Tuesday, February 16, 2010
SQL Server - Insert Data From Linked Server Table
Subscribe to:
Posts (Atom)
Big Data & SQL
Hi Everybody, Please do visit my new blog that has much more information about Big Data and SQL. The site covers big data and almost all the...
-
There is a ‘ CharIndex’ function in SQL Server which is similar to the Oracle ‘ Instr’ function. In Oracle, the syntax of the INSTR functi...
-
The CONCAT() function joins the input strings or different column values and returns as a single string. However, if we need to concatenate ...
-
Can we create a table based on a view in Hive? Yes, we can.!! Let's create "View" by combining Emp and Dept tables. -~ To crea...