通过在不同服务器上的两个数据库中连接两个表来查询数据 [英] Querying data by joining two tables in two database on different servers

问题描述

在不同服务器上的两个不同数据库中有两个表，我需要连接它们以便进行少量查询。我有什么选择？我应该怎么办？

解决方案

您需要使用 sp_addlinkedserver 创建服务器链接。有关使用情况，请参见参考文档。一旦建立了服务器链接，您将构建正常的查询，只是将数据库名称添加到其他服务器的前缀。 IE：

   -  FROM DB1 
 SELECT * 
 FROM [MyDatabaseOnDB1]。[dbo] MyTable] tab1 
 INNER JOIN [DB2]。[MyDatabaseOnDB2]。[dbo]。[MyOtherTable] tab2 
 ON tab1.ID = tab2.ID 
  pre> 
 
 
链接建立后，您还可以使用 OPENQUERY 在远程服务器上执行SQL语句，只将数据传回给您。这可以更快一点，它将让远程服务器优化您的查询。如果在上面的示例中将数据缓存在 DB1 中的临时（或内存）表中，则可以查询它，就像加入标准表。例如：
   - 从其他数据库服务器获取数据
 SELECT * 
 INTO #myTempTable 
 FROM OPENQUERY（[DB2]，'SELECT * FROM [MyDatabaseOnDB2]。[dbo]。[MyOtherTable]'）
 
  - 现在我可以加入我的临时表来查看数据
 SELECT * FROM [MyDatabaseOnDB1]。[dbo]。[MyTable] tab1 
 INNER JOIN #myTempTable tab2 ON tab1.ID = tab2.ID 
  
请查看 OPENQUERY的文档查看更多示例。上面的例子很漂亮。我肯定会使用第一个方法在这个具体的例子，但第二个选项使用 OPENQUERY 可以节省一些时间和性能，如果你使用查询过滤掉一些数据。 p> 
There are two tables in two different databases on different servers, I need to join them so as to make few queries. What options do I have? What should I do?
解决方案
You'll need to use sp_addlinkedserver to create a server link.  See the reference documentation for usage.  Once the server link is established, you'll construct the query as normal, just prefixing the database name with the other server.  I.E:
-- FROM DB1
SELECT *
FROM [MyDatabaseOnDB1].[dbo].[MyTable] tab1
    INNER JOIN [DB2].[MyDatabaseOnDB2].[dbo].[MyOtherTable] tab2
        ON tab1.ID = tab2.ID
Once the link is established, you can also use OPENQUERY to execute a SQL statement on the remote server and transfer only the data back to you.  This can be a bit faster, and it will let the remote server optimize your query.  If you cache the data in a temporary (or in-memory) table on DB1 in the example above, then you'll be able to query it just like joining a standard table.  For example:
-- Fetch data from the other database server
SELECT *
INTO #myTempTable
FROM OPENQUERY([DB2], 'SELECT * FROM [MyDatabaseOnDB2].[dbo].[MyOtherTable]')

-- Now I can join my temp table to see the data
SELECT * FROM [MyDatabaseOnDB1].[dbo].[MyTable] tab1
    INNER JOIN #myTempTable tab2 ON tab1.ID = tab2.ID
Check out the documentation for OPENQUERY to see some more examples.  The example above is pretty contrived.  I would definitely use the first method in this specific example, but the second option using OPENQUERY can save some time and performance if you use the query to filter out some data.

                        
这篇关于通过在不同服务器上的两个数据库中连接两个表来查询数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！