如何从两个不同的sql服务器获取数据?
我有一个内联查询,其中 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 ?
Thanks in advance !!!
是的,在一台服务器上设置到另一台服务器的链接服务器。然后你就可以用连接来做一个普通的查询。它看起来像这样:
这假设您正在 Server1 上运行查询。您还可以拥有两个链接服务器,并使用 [servername].[dbname].[schema].[table] 引用它们,然后像平常一样在 SQL 中使用。
或者,您可以使用 OPENROWSET (但如果您'重新能够设置)。 OpenRowSets 看起来像这样:
然后您可以像本地表一样加入“a”。在幕后,它可能会将所有数据拉到本地数据库,因此您应该考虑将 WHERE 添加到内部查询以限制行,并且仅获取您需要的列。
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:
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.
Alternatively, you can use OPENROWSET (but linked server is easiest if you're able to set that up). OpenRowSets look like this:
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.