How does OPENQUERY differ for SELECT and INSERT?

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

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 1A 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 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 utf8SET character_set_results = NULLSET SQL_AUTO_IS_NULL = 0set @@sql_select_limit=1select * from insert_test where 1=0select * from insert_test where 1=0INSERT 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.