如何从两个不同的 sql 服务器获取数据? [英] How to fetch data from two different sql servers?

问题描述

我有一个内联查询，其中我在 server1 中有一个 table1，在 server2 中有另一个 table2.我需要加入这两个表，并获取数据.

I have an inline query, in which I have one table1 in server1 and another table2 in server2. I need to join these two tables, and fetch data.

我可以这样做，例如连接到一台服务器、获取数据并连接到下一台服务器...获取数据.并加入他们.

I can do this like connect to one server, get data and connect to next server...fetch data. and join them.

但是有没有其他更好的方法.我听说过链接服务器.这对这里有帮助吗?

But is there any other better way. I have heard about Linked servers. Will that help here ?

提前致谢！！！

推荐答案

是的，在一台服务器上设置一个链接服务器到另一台服务器.然后你可以用一个连接做一个普通的查询.它看起来像这样:

Yes, set up a linked server on one server to the other. Then you can just do a normal query with a join. It would look something like this:

SELECT t1.Col1
    ,  t2.ColA
FROM server1Table t1
INNER JOIN SERVER2.dbname.dbo.tableName t2 ON t1.TheId = t2.TheId

这假设您在 Server1 上运行查询.您还可以拥有两个链接服务器并使用 [servername].[dbname].[schema].[table] 引用它们，然后在 SQL 中正常使用.

this assumes you're running the query on Server1. You can also have two linked servers and reference them both using [servername].[dbname].[schema].[table] and then use in SQL as normal.

或者，您可以使用 OPENROWSET(但链接服务器最简单如果你能够设置它).OpenRowSet 如下所示:

Alternatively, you can use OPENROWSET (but linked server is easiest if you're able to set that up). OpenRowSets look like this:

SELECT t1.Col1
    ,  t2.ColA
FROM server1Table t1
INNER JOIN  OPENROWSET('SQLNCLI', 'Server=Server2;Trusted_Connection=yes;',
                       'SELECT t2.ColA, t2.TheId FROM dbname.dbo.tableName') AS t2
  ON t1.TheId = t2.TheId

然后你就可以加入'a'，就好像它是一个本地表一样.在引擎盖下，它可能会将所有数据拉到本地数据库，因此您应该考虑将 WHERE 添加到内部查询以限制行，并且只获取您需要的列.

and then you can just join on 'a' as if it's a local table. Under the hood it's probably pulling all the data down to your local database, so you should consider adding WHERE to the inner query to restrict rows, and only get the columns you need.

这篇关于如何从两个不同的 sql 服务器获取数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！