MySQL SELECT 和 INSERT 的 OPENQUERY 有何不同？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15199249/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How does OPENQUERY differ for SELECT and INSERT?
提问by Abs
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?
如果是前者，那么这是非常低效的。我应该限制返回的结果集，这会影响我的INSERT吗？
This is basically a question on how OPENQUERYworks when it comes to SELECTand INSERT.
这基本上是如何一个问题OPENQUERY的作品，当谈到SELECT和INSERT。
I appreciate any help.
我很感激任何帮助。
采纳答案by Zelloss
Not sure what you try to accomplish with your INSERT.
不确定您尝试使用 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:
选择只会延迟您的插入检索选择查询返回的内容（无济于事），而不会为您提供其他信息。此外，使用 openquery，您可以使用更正确的语法来插入：
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:
但是，如果您尝试将 select 检索到的值插入到 LOCAL 服务器，则语法应为：
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
就足够了
回答by PseudoToad
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.
SELECT 将返回记录，INSERT 不会返回结果集，除了受影响的记录数。这可以通过使用 SET NOCOUNT ON 来抑制；但是，我不确定抑制是指可见性还是实际出现的行数。
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 返回的记录，实现这一点的唯一方法是使用 OUTPUT 子句。客户端计算机将无法访问 OUTPUT INSERTED 行，因此无法返回这些行。如果您尝试运行以下命令，您将收到错误消息：
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'
回答by patrickmdnet
When you use OPENQUERYto perform an INSERT, it will perform the SELECTfirst and throw away the results. It uses the column metadata from the result set to determine how to send the INSERTcommand. For that reason you should always add where 1=0to the end of the SELECT.
当您使用OPENQUERY执行 时INSERT，它将执行第SELECT一个并丢弃结果。它使用结果集中的列元数据来确定如何发送INSERT命令。出于这个原因，您应该始终添加where 1=0到SELECT.
From SQL Server to MySQL the proper syntax is:
从 SQL Server 到 MySQL，正确的语法是：
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:
如果您在 MySQL 端激活常规查询日志，您将看到这些命令来自 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.
所以你可以看到选择被执行了两次。如果没有where 1=0，MySQL 服务器将返回所有行。
