OPENQUERY在SELECT和INSERT操作中有什么区别？

Question

我知道以下查询将从链接服务器中拉取结果集：

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

然而，当涉及到插入时，情况是否相同？它会拉下结果集还是只获取列信息？

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

如果是前者，那么这非常低效。我应该限制返回的结果集吗？这会影响我的INSERT吗？

基本上，这是一个关于OPENQUERY在SELECT和INSERT方面工作方式的问题。

感谢任何帮助。

- Abs

Answer 1

不确定您试图通过INSERT完成什么目标。

如果您想在远程服务器上进行插入操作，则正确的语法应该是：

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

使用 SELECT 只会延迟插入操作的获取，无论 SELECT 查询返回什么信息（都没有用），并且不提供额外的信息。此外，使用 openquery，您可以使用以下语法进行更正确定的插入：

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

但是，如果你想将从SELECT语句检索到的值插入到本地服务器中，语法应该是：

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

是的，该语句将插入值，而不仅仅是列信息。

如果您只想在本地复制表格，可以使用简单的

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;

足矣

- Zelloss

---

只是提供信息：第二个例子中的四部分命名语法仅在连接服务器类型已经注册到分布式事务中时才起作用。（大多数情况下都已经注册了，只是需要注意一下。） - Kyle Hale

通过您的第一个查询，您是如何确定要插入哪些列而不通过select语句指定这些列的呢？ - Abs

我完成完整的插入，仅留下具有标识数据或默认数据的列（大多数情况下我也编写默认数据）。通常将整个记录集提供给插入操作会更快，因为它会将其作为数据块写入。openquery假定列按照插入顺序排列（与普通插入操作一样）。如果您需要指定非连续列，则需要使用select，但代价是性能。 - Zelloss

我认为这个答案是正确的，因为INSERT中的SELECT将在链接服务器上执行选择并返回结果集。您能否更正您的句子“插入检索首先记录集”-它不仅会获取第一行，而是获取选择查询返回的任何内容。 - Abs

Answer 2

在 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)

关于从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'

- PseudoToad

---

当我提到“INSERT”时，实际上是指在“OPENQUERY”中的“SELECT”。此查询在链接服务器上执行，然后将在本地服务器上执行“INSERT”。但是，SQL服务器会意识到这是一个“INSERT”，因此不会从链接服务器返回结果集吗？ - Abs

我刚刚运行了相同的语句并在每个端口上观察了分析器。以上是结果，这让我相信SELECT仅在被OPENQUERY调用的服务器上发生，并且该SELECT更像是插入的模板而不是其他任何东西。 - PseudoToad

Answer 3

当使用OPENQUERY进行INSERT操作时，它会先执行SELECT并丢弃结果。然后，它使用结果集中的列元数据确定如何发送INSERT命令。因此，您应该始终在SELECT语句的末尾添加where 1=0。

从SQL Server到MySQL的正确语法是：

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

如果您在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')

因此，您可以看到选择操作被执行了两次。没有where 1=0，MySQL服务器将返回所有行。

- patrickmdnet