Linked Servers: Performing Select, Insert, Update and Delete (CRUD) actions
You could literally spend hours combing the internet trying to figure out how to do something.
In my last post, I spend 4 hours trying to figure out exactly how to get a linked server working with SQL Server. After I finally getting the server linked so I could perform simple queries against it, I needed to now figure out how I could perform inserts, updates and deletes to it.
Scouring the internet again proved totally a waste of time and I decide to just try to figure it out on my own. Well after another 2 hours, I have figured it out. Here is the syntax to do select, insert, update, and delete (CRUD) actions against a linked server. Remember that your syntax might vary slightly. All queries use the OPENQUERY command.
select * from openquery(‘my_linked_server’, ‘select * from table_schema.table_name’)
insert openquery(‘my_linked_server, ‘select column_1, column2 from table_schema.table_name’)
values (‘my_value1’, ‘my_value2’)
update openquery(‘my_linked_server, ‘select column_1, column_2 from table_schema.table_name where pk = pk_value’)
set column_1 = ‘my_value1’, column_2 = ‘my_value2’
delete from openqueryopenquery(‘my_linked_server, ‘select * from table_schema.table_name where pk = pk_value’)
There you have it, I just saved you 2 hours (can I get mine back).