sql-server - SQL Server : OPENQUERY in easy words (with example)

Question

我是 SQL Server 的新手，我在这里偶然发现了这一点:

BEGIN TRANSACTION loadTTAllocations

    INSERT INTO @ttaAllocs
        SELECT 
            company_code AS CompanyId,
            job_number AS JobNo,
            ...
            is_blocked AS IsBlocked
        FROM 
            OPENQUERY([SRV_TimeTac], 
                       'SELECT DISTINCT 
                            job.individual_value_1 AS job_number,
                            job.individual_value_3 AS company_code,
                            ...
                            now() AS queryTimeStamp
                        FROM 
                            pm_altran.pm_tasks_subprojects AS taskCode
                        INNER JOIN 
                            pm_altran.pm_tasks_subprojects AS job
                                 ON job.id = taskCode.mother_id 
                                 AND job.is_done = 0
                                 AND NOT job.is_blocked
                        INNER JOIN 
                            pm_node_to_user AS n2u
                                 ON n2u.node_id = taskCode.id
                                 AND n2u.access = 1
                                 AND n2u.is_todo = 1
                        LEFT JOIN 
                            altran_pm_user_user_settings AS u
                                 ON u.administrators_id = n2u.user_id
                        WHERE 
                            taskCode.object_type = ''task''
                            AND taskCode.is_paid_non_working = 0
                            AND taskCode.id > 50');

    SET @rowCount = @@ROWCOUNT

    SET @eventDetails = 'End loadTTAllocations: ' + CAST(@rowCount as VARCHAR(10)) + ' rows affected';

    COMMIT TRANSACTION loadTTAllocations

问题是 OPENQUERY .

我从文档中了解到的基本上是，它只是在另一台服务器上的查询。在这种情况下，SRV_TimeTac？

所以在这个例子中，我们在另一个名为“SRV_TimeTac”的服务器上查询并返回最终加载到名为@ttaAllocs 的临时表中的结果。

这样对吗？

感谢您的帮助。

Answer 1

OPENQUERY (Transact-SQL) :

Executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

Arguments

linked_server
Is an identifier representing the name of the linked server.

' query '
Is the query string executed in the linked server. The maximum length of the string is 8 KB.

因此，要回答您的问题，是的，这就是您的查询正在做的事情。您的情况下linked_server 的值是 SRV_TimeTac ' query ' 的值是你的长字符串。 “查询”的值在链接服务器上运行，结果集返回到您运行的服务器 OPENQUERY在。然后将该结果集插入到变量 @ttaAllocs 中。 .

你有趣的话 COMMIT您在 INSERT 之前开始的交易，考虑到您仅将值插入到变量中并且没有影响任何持久对象，这似乎很奇怪。表变量很可能最终被写入磁盘，而不是存储在内存中，如果它足够大，那么您很可能会将事务提交到 tempdb .