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

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?

在不同的服务器上的两个不同的数据库中有两个表，我需要将它们连接起来，以便进行少量查询。我有什么选择?我应该做什么?

10 个解决方案

#1

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:

您需要使用sp_addlinkedserver来创建一个服务器链接。请参阅参考文档以了解使用情况。一旦建立了服务器链接，您将像往常一样构造查询，只需在另一台服务器前加上数据库名。即:

-- 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:

一旦建立了链接，您还可以使用OPENQUERY在远程服务器上执行SQL语句，并只将数据传输回您。这可能会更快一点，并且可以让远程服务器优化您的查询。如果在上面的示例中将数据缓存到DB1上的临时(或内存中)表中，那么您将能够像查询标准表一样查询数据。例如:

-- 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.

请参阅OPENQUERY的文档，以查看更多示例。上面的例子是精心设计的。在这个特定的示例中，我肯定会使用第一个方法，但是如果使用查询过滤一些数据，使用OPENQUERY的第二个选项可以节省一些时间和性能。

#2

Try this:

试试这个:

SELECT tab2.column_name  
FROM  [DB1.mdf].[dbo].[table_name_1] tab1 INNER JOIN [DB2.mdf].[dbo].[table_name_2]  tab2   
    ON tab1.col_name = tab2.col_name

#3

If a linked server is not allowed by your dba, you can use OPENROWSET. Books Online will provide the syntax you need.

如果您的dba不允许链接服务器，您可以使用OPENROWSET。在线书籍将提供您需要的语法。

#4

A join of two tables is best done by a DBMS, so it should be done that way. You could mirror the smaller table or subset of it on one of the databases and then join them. One might get tempted of doing this on an ETL server like informatica but I guess its not advisable if the tables are huge.

两个表的连接最好由DBMS完成，因此应该这样做。您可以在其中一个数据库上镜像较小的表或它的子集，然后加入它们。有人可能会在像informatica这样的ETL服务器上这样做，但我认为如果表很大，那么这样做是不可取的。

#5

From a practical enterprise perspective, the best practice is to make a mirrored copy of the database table in your database, and then just have a task/proc update it with delta's every hour.

从实际的企业角度来看，最佳实践是在数据库中创建数据库表的镜像副本，然后让任务/proc每小时更新一次。

#6

If the database link option is not available, another route you could take is to link the tables via ODBC to something such as MS Access or Crystal reports and do the join there.

如果数据库链接选项不可用，您可以采取的另一种方法是通过ODBC将表链接到诸如MS Access或Crystal报表之类的东西，并在其中进行连接。

#7

I tried this code below and it's working fine

我尝试了下面的代码，它运行得很好

SELECT        TimeTrackEmployee.StaffID
FROM            dbo.tblGBSTimeCard AS GBSTimeCard INNER JOIN
                         TimeTrak.dbo.tblEmployee AS TimeTrackEmployee ON GBSTimeCard.[Employee Number] = TimeTrackEmployee.GBSStaffID

#8

You could try the following:

你可以试试下面的方法:

select customer1.Id,customer1.Name,customer1.city,CustAdd.phone,CustAdd.Country
from customer1
inner join [EBST08].[Test].[dbo].[customerAddress] CustAdd
on customer1.Id=CustAdd.CustId

#9

Maybe hard-coded database names isn't the best approach always within an SQL-query. Thus, adding synonyms would be a better approach. It's not always the case that databases have the same name across several staging environments. They might consist by postfixes like PROD, UAT, SIT, QA and so forth. So be aware of hard-coded queries and make them more dynamic.

也许硬编码的数据库名称并不是sql查询中最好的方法。因此，添加同义词将是一种更好的方法。数据库在多个登台环境中具有相同名称的情况并不总是存在。它们可能由诸如PROD、UAT、SIT、QA等后缀组成。所以要注意硬编码的查询，使它们更加动态。

Approach #1: Use synonyms to link tables between databases on the same server.

方法#1:使用同义词在同一服务器上的数据库之间链接表。

Approach #2: Collect data separately from each database and join it in your code. Your database connection strings could be part of your App-server configuration through either a database or a config file.

方法#2:分别从每个数据库收集数据，并将其连接到代码中。通过数据库或配置文件，您的数据库连接字符串可以是应用程序-服务器配置的一部分。

#10

-1

for this simply follow below query

为此，请遵循下面的查询

select a.Id,a.type,b.Name,b.City from DatabaseName.dbo.TableName a left join DatabaseName.dbo.TableName b on a.Id=b.Id

Where I wrote databasename, you have to define the name of the database. If you are in same database so you don't need to define the database name but if you are in other database you have to mention database name as path or it will show you error. Hope I made your work easy

在我编写databasename时，您必须定义数据库的名称。如果您在同一个数据库中，那么您不需要定义数据库名称，但是如果您在其他数据库中，您必须提到数据库名称作为路径，否则它将显示您的错误。希望我让你的工作轻松。

#1

-- FROM DB1
SELECT *
FROM [MyDatabaseOnDB1].[dbo].[MyTable] tab1
    INNER JOIN [DB2].[MyDatabaseOnDB2].[dbo].[MyOtherTable] tab2
        ON tab1.ID = tab2.ID

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

#2