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:

select * from openquery(‘my_linked_server’, ‘select * from table_schema.table_name’)

INSERT:

insert openquery(‘my_linked_server, ‘select column_1, column2 from table_schema.table_name’)
values (‘my_value1′, ‘my_value2′)

UPDATE:

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:

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).