Rip's Domain

Linked Servers: Performing Select, Insert, Update and Delete (CRUD) actions

Posted in SQL, TechSupport by rip747 on December 14, 2007

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

9 Responses

Subscribe to comments with RSS.

  1. Dave said, on March 20, 2008 at 8:01 am

    Nice job – books online was startlingly unhelpful, as usual.
    You have indeed saved me at least two hours.

    Keep up the good work.

  2. rip747 said, on March 20, 2008 at 9:41 pm

    It’s what I’m here for😉

  3. Magnus said, on April 8, 2008 at 2:07 am

    Well, why not use LinkedServerName.Database.dbo.TableName. There is however some interesting performance issues to take into consideration when inserting into a linked server. It seems as if SQL does the inserts post by post.

  4. Milan said, on January 4, 2009 at 6:14 pm

    Thank you. You saved my time.

  5. Mike Hankey said, on September 13, 2009 at 5:28 pm

    Thanks, saved me a bunch of time.
    Just learning SQL and setting up Linked Servers.

    Mike

  6. Gaius Gracchus said, on April 21, 2011 at 3:00 pm

    Regarding what Magnus said above:
    “Well, why not use LinkedServerName.Database.dbo.TableName. ”

    Well, because it does not work.

    Note that this facility would depend upon what capabilities the driver provides for a given linked server. So if you are just linking two SQL Server dbs together, yeah, maybe that would work. But if you are linking to MySQL, guess what happens when you try that:

    —————————–
    Invalid use of schema or catalog for OLE DB provider “MSDASQL” for linked server “wqx”. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
    —————————–

    So this error tells us the ‘provider’ (i.e., data source driver) does not give us this interface for this database.

  7. Gaius Gracchus said, on April 21, 2011 at 3:01 pm

    Thanks to the person who left this information. You have helped a lot of people with your efforts.

  8. gaiusgracchus said, on April 22, 2011 at 7:26 pm

    Magnus said, on April 8, 2008 at 2:07 am

    Well, why not use LinkedServerName.Database.dbo.TableName.
    ——————————————————————————-

    Because that does not work with many types of database drivers. It will produce an error with drivers that do not support that syntax, such as mysql.

  9. daniel gonzalez said, on June 14, 2012 at 10:49 am

    thx u.

    a long time searching for one solution a my problem and this post make it!!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: