SQL Server 2008 R2 – Insert Into Existing Table Using OpenQuery

insertsql-server-2008-r2t-sql

I am attempting to insert data into a linked server using openquery. I have attempted the below syntax, but get an error of

Cannot get the column information from OLE DB provider "MSDASQL" for linked server "Aurora".

What is the proper way to write this syntax so that my update statement will execute as it should?

INSERT INTO OPENQUERY(AURORA, 'Select firstname, lastname, address, city, state from testdb.address_info') VALUES
('jo', 'bob', '11111 no where drive', 'city1', 'state1')

Best Answer

Provided that AURORA is a linked server, maybe you could simplify the operation by eliminating the OPENQUERY construct?

INSERT INTO AURORA.testdb.some_schema.address_info (firstname, lastname, address, city, state)
VALUES ('jo', 'bob', '11111 no where drive', 'city1', 'state1');

You might have to set/change some of the connection options on the linked server for this to work properly.

Note that you seem to be missing the schema name or the database name; when you're referencing a linked server, the four-part naming is server.database.schema.object, where you can choose to leave the schema blank to use the default schema (like dbo).

Related Question