如何从两个不同的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 (但链接服务器最简单如果您能够进行设置). OpenRowSets看起来像这样:

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屋！