Insert into a Linked Server DB
-
Gurus I have a task for the day I need to come up with a solution.
1)I have two linked servers (both teradata)
2) I have to select fields from different tables from both servers and merge them into one table into one of the servers
For example:
select A.field1, A.field2, B.field1, B.field2
from openquery(LnkSrv1,'select field1, field2 from db.table1') A
inner join (select field1, field2 from openquery(LnkSrv2,'select field1, field2 from db.table1')) B on
A.field3 = B.field3
The result set should go to LknSrv2.
How the design the OPENQUERY INSERT?
-
-
I don't think you can insert rows into a linked server using openquery statement.
If linked servers are RDBMS as SQL Server, MS Access Oracle or else, you can insert row by referencing remote table as following :
linkedServername...tableNameInRemoteServer
For example, my linked server is named A and it have a table named T.
You can retrieve infomation with this request : select * from A...T
Also, you can update information with update A...T set ...
Obviously, it's the same thing for insert statement
Notice : The 3 dots are important, you can use this syntaxe only on relation database
Fabrice for your service
Kindest Regards,fabricej
-
don't think you can insert rows into a linked server using openquery statement. Yes you can
From LnkSrv1
INSERT INTO OPENQUERY(LnkSrv2,'SELECT field1,field2,field3,field4 FROM db.table1')
(field1,field2,field3,field4)
SELECT a.field1, a.field2, b.field1, b.field2
FROM OPENQUERY(LnkSrv1,'SELECT field1, field2 FROM db.table1') b
INNER JOIN (SELECT field1, field2 FROM OPENQUERY(LnkSrv2,'SELECT field1, field2 FROM db.table1')) b
ON a.field3 = b.field3
From LnkSrv2 (better)
INSERT INTO db.table1
(field1,field2,field3,field4)
SELECT a.field1, a.field2, b.field1, b.field2
FROM db.table1 a
INNER JOIN OPENQUERY(LnkSrv1,'SELECT field1,field2 FROM db.table1') b
ON a.field3 = b.field3
Or as fabricej states use four part naming (even better)
Far away is close at hand in the images of elsewhere.
Anon. -
Oh Ok David,
I didn't know it was possible, now I know.
It's good. I learn things by helping people.
Kindest Regards,fabricej
-
I think you are saying server A is Teradata, server B is Teradata, these are linked to server C which is SQL. You want to join data in server A and B and store it on server A, using server C to do the work.
You will get FAR better performance if you use the networking facilities in Teradata to access server B from server A. This is because you will only be moving data for server B, and only moving it once.
Your solution requires you to get data from server A and server B, then write it to server A. This can work, but will always be slow.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Teradata servers A and B may not be able to talk to each other, so you may have to do it through a SQL Server intermediary.
You can use INSERT INTO OPENQUERY as pointed out above, but you can also do it without the OPENQUERY functions.
INSERT INTO TeradataLinkA..Database.OtherTable
SELECT ...
FROM TeradataLinkA..Database.Table a
INNER JOIN TeradataLinkB..Database.Table b ON ...
For some reason Teradata linked servers expect to see the database name in the schema part of the four-part reference.
I don't know if there are any performance differences using direct references vs. OPENQUERY. I have to use a remote Teradata server over a very slow encrypted VPN connection, and performance sucks. With the Teradata ODBC driver it was abysmal, even Access was better than the SQL linked server with the OLEDB provider for ODBC. The newer Teradata OLEDB provider is better, but we use the Teradata FastExport and FastImport tools for all non-trivial data transfers. Hopefully you have more bandwidth to play with than I do.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply