spoon 不同库实现表连接查询,通过在不同服务器上的两个数据库中连接两个表来查询数据...

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.