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.

Answer 1

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