How Can I Join Tables Across Databases on Different Servers?

Join tables across different server databases

Integrating data from different server databases provides the possibility to perform comprehensive queries. To do this, you can consider the following strategies:

Use sp_addlinkedserver

Use sp_addlinkedserver to create a server link to establish a connection between two databases. Please refer to the relevant documentation to learn how to use it. Once the link is established, you can build the query as usual, prepending the database name with the linked server name, like this:

<code class="language-sql">SELECT *
FROM [MyDatabaseOnDB1].[dbo].[MyTable] tab1
INNER JOIN [DB2].[MyDatabaseOnDB2].[dbo].[MyOtherTable] tab2
ON tab1.ID = tab2.ID</code>

USE OPENQUERY

OPENQUERY allows you to execute SQL statements on a remote server and retrieve only the required data. This approach improves speed and allows the remote server to optimize queries. Caching data in a temporary table in the local database can facilitate subsequent queries, similar to connecting to a standard table.

For example:

<code class="language-sql">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</code>

Please refer to the documentation of OPENQUERY for more examples. While the example provided here is simpler, the first approach using sp_addlinkedserver may be more suitable. However, when filtering data is required, OPENQUERY can improve performance.

The above is the detailed content of How Can I Join Tables Across Databases on Different Servers?. For more information, please follow other related articles on the PHP Chinese website!