How Can I Join Tables Across Different Database Servers?

Query data across database server connection tables

In many cases, you may need to join tables in different databases located on different servers. This can be a challenge, but there are ways to facilitate this data integration.

Method 1: Create a server link

sp_addlinkedserver Stored procedures allow you to establish a connection between a local server and a remote database server, called a server link. Once the link is established, you can reference the remote table in a query by prepending the remote server's database name with its database name.

For example, if you have table MyDatabaseOnDB1 in MyTable and table MyDatabaseOnDB2 in MyOtherTable, you can join them using the following query:

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

Method 2: Use OPENQUERY

Alternatively, you can use OPENQUERY to execute a SQL query on a remote server and retrieve the results. This method allows the remote server to optimize queries and can improve performance. To do this, you create a temporary table on the local server and use OPENQUERY to populate it with the results of the query executed on the remote server.

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>

The use of

OPENQUERY can improve performance and save time, especially when using queries to filter large data sets.

Whether you use server links or OPENQUERY depends on your application's specific requirements and performance considerations. Both approaches provide viable solutions for querying data across database servers and facilitate efficient data integration.

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