How does OPENQUERY differ for SELECT and INSERT?

Question

I'm aware that the following query will pull down the result set from a linked server:

SELECT * FROM openquery(DEVMYSQL, 
    'SELECT event_id, people_id, role_id, rank, last_updated FROM event_cast')

However, is this the same case when it comes to inserting? Will it pull down the result set or will it just get the column information?

INSERT INTO openquery(DEVMYSQL, 
     'SELECT event_id, people_id, role_id, rank, last_updated FROM event_cast')

If the former, then this is very inefficient. Should I limit the result set returned and will this effect my INSERT?

This is basically a question on how OPENQUERY works when it comes to SELECT and INSERT.

I appreciate any help.

Zelloss · Accepted Answer · 2013-03-11T17:30:08.717

10

Not sure what you try to accomplish with your INSERT.

The correct syntax (if you want to insert on the REMOTE server) should be

INSERT into openquery(MyServer, 'dbo.event_cast') values ('','')

The select only delays your insert retrieving what ever the select query returns (to no avail) without giving you additional info. Also, with openquery you could use this syntax, more correct, for the insert:

INSERT into myserver.mydatabase.dbo.event_Cast values('','')

But, if you are trying to insert into the LOCAL server the values retrieved by the select the syntax should be:

INSERT into dbo.my_localtable SELECT * FROM openquery(DEVMYSQL, 'SELECT event_id, people_id, role_id, rank, last_updated FROM event_cast')

And yes, the sentence will insert the values, not only the column information.

If you only want to replicate the table locally a simple

SELECT top 1 * into new_local_event_cast FROM openquery(DEVMYSQL, 'SELECT event_id, people_id, role_id, rank, last_updated FROM event_cast');
TRUNCATE TABLE new_local_event_cast;

will suffice

edited Mar 11 '13 at 17:30

answered Mar 07 '13 at 20:59

Zelloss

568
3
12

2

Just FYI: The four-part naming syntax in the second example only works if the Linked Server type has been enrolled into Distributed Transactions. (Most are, just a gotcha.) – Kyle Hale Mar 07 '13 at 21:08
With your first query, how are you determining the columns to insert into without specifying the columns via a select? – Abs Mar 08 '13 at 09:49
I do the complete insert leaving only out the the columns with the identity data or default data (most times I write the default data too). Usually is faster to give the whole recordset to insert as it writes it as a chunk of data. The openquery assume that the cols are in the inserting order (as the normal insert do). True: if you need to especify non consecutive cols you need to use the select, but at a cost of performance – Zelloss Mar 08 '13 at 13:18
1

I think this answer is correct as the select in the `INSERT` will perform the select on the linked server and return the result set. Could you correct your sentence "insert retrieving first the recordset" - it won't just get the first row it gets back what ever the select query returns. – Abs Mar 11 '13 at 16:47

PseudoToad · Answer 2 · 2013-03-07T14:55:03.643

Where the SELECT will return records, the INSERT will not return a result set except for the count of records affected. This can be suppressed by using SET NOCOUNT ON; however, I am not sure if suppression would refer to visibility or the row count actually coming over.

    INSERT INTO OPENQUERY(MYSERVER, 'SELECT [Drive_Letter] ,[MBFree],[Server] FROM [Blah].[dbo].[SQL_Drives]')
    SELECT 'X', 2, 'MyServer'

(1 row(s) affected)

As for records being returned from the INSERT, the only way to make that happen is to use a OUTPUT clause. The client machine will not have access to the OUTPUT INSERTED rows so those cannot be returned. If you try to run the following, you will receive an error:

INSERT INTO OPENQUERY(MYSERVER, 'SELECT [Drive_Letter] ,[MBFree],[Server] FROM [BLAH].[dbo].[SQL_Drives]')
OUTPUT INSERTED.*
SELECT 'X', 2, 'MyServer'

Msg 405, Level 16, State 1, Line 1
A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.


 -- EDIT RESULTS OF PROFILER ---------------------------      


-- Statements that occured on server called through OPENQUERY
    exec sp_cursoropen @p1 output,N'SELECT [Drive_Letter] ,[MBFree],[Server] FROM [MyServer].[dbo].[SQL_Drives]',@p3 output,@p4 output,@p5 output
    select @p1, @p3, @p4, @p5

    exec sp_cursor 180150009,4,0,N'[MyServer].[dbo].[SQL_Drives]',@Drive_Letter='X',@MBFree=2,@Server='MyServer'


--Statements that occured on client
    INSERT INTO OPENQUERY(MyServer, 'SELECT [Drive_Letter] ,[MBFree],[Server] FROM [MyServer].[dbo].[SQL_Drives]')
    SELECT 'X', 2, 'MyServer'

When I refer to `INSERT`, I am really talking about the `SELECT` within the `OPENQUERY`. This query is executed on the linked server and then the `INSERT` will be executed on the local server. However, will SQL server realise that it is an `INSERT` and therefore not return the result set from the linked server? — Abs, Mar 07 '13 at 09:38
I just ran the same statements and watched profiler on each end. The results are above, leading me to believe that the SELECT only occurs on the server being called by the OPENQUERY and that the SELECT acts more as a template for the insert than anything else. — PseudoToad, Mar 07 '13 at 14:53

score 2 · Answer 3 · answered Jul 07 '16 at 17:13

When you use OPENQUERY to perform an INSERT, it will perform the SELECT first and throw away the results. It uses the column metadata from the result set to determine how to send the INSERT command. For that reason you should always add where 1=0 to the end of the SELECT.

From SQL Server to MySQL the proper syntax is:

insert into openquery(MYSQLDEV, 'select * from insert_test where 1=0') values ('test');

If you activate the general query log on the MySQL side you'll see these commands coming from SQL Server:

SET NAMES utf8
SET character_set_results = NULL
SET SQL_AUTO_IS_NULL = 0
set @@sql_select_limit=1
select * from insert_test where 1=0
select * from insert_test where 1=0
INSERT INTO `database`.`insert_test`(`column`) VALUES ('test')

So you can see that the select is being performed twice. Without the where 1=0, all the rows will be returned by the MySQL server.

How does OPENQUERY differ for SELECT and INSERT?

3 Answers3