SQL 如何使用一个SELECT语句从两台服务器的两个数据库中获取数据？

How to get data from two databases in two servers with one SELECT statement?

提问by Kalina

I don't actually want to modify either database, just get the data.

我实际上不想修改任何一个数据库，只是获取数据。

I know how to connect to each database individually, with these connection strings:

我知道如何使用这些连接字符串单独连接到每个数据库：

Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;Persist Security Info=False;

Provider=OraOLEDB.Oracle.1;Data Source={0};User ID={1};Password={2};Persist Security Info=True;

But how can I get this overlapping data together? Is that even possible, especially considering that one is Oracleand one is SQL Server? Or would it be better to do the SELECT statements on each database individually and then match them after?

但是我怎样才能把这些重叠的数据放在一起呢？这甚至可能吗，尤其是考虑到一个是Oracle一个是SQL Server？或者最好在每个数据库上单独执行 SELECT 语句，然后再匹配它们？

---

---

For example, how would I get all students that are 10 years old and like the color blue?

例如，我将如何获得所有 10 岁且喜欢蓝色的学生？

Notice that all items in DatabaseB have an ID that maps to DatabaseA, but not the other way around.

请注意，DatabaseB 中的所有项目都有一个映射到 DatabaseA 的 ID，但反之则不然。

回答by Namphibian

I have done this with MySQL,Oracle and SQL server. You can create linked servers from a central MSSQL server to your Oracle and other MSSQL servers. You can then either query the object directly using the linked server or you can create a synonymn to the linked server tables in your database.

我已经用 MySQL、Oracle 和 SQL 服务器做到了这一点。您可以创建从中央 MSSQL 服务器到 Oracle 和其他 MSSQL 服务器的链接服务器。然后，您可以直接使用链接服务器查询对象，也可以为数据库中的链接服务器表创建同义词。

Steps around creating and using a linked server are:

创建和使用链接服务器的步骤是：

1. On your "main" MSSQL server create two linked servers to the servers that contains the two databases or as you said database A and database B.
2. You can then query the tables on the linked servers directly using plain TSQL select statements.

1. 在您的“主”MSSQL 服务器上，为包含两个数据库的服务器创建两个链接服务器，或者如您所说的数据库 A 和数据库 B。
2. 然后，您可以使用普通的 TSQL 选择语句直接查询链接服务器上的表。

To create a linked server to Oracle see this link: http://support.microsoft.com/kb/280106

要创建到 Oracle 的链接服务器，请参阅此链接：http: //support.microsoft.com/kb/280106

A little more about synonyms. If you are going to be using these linked server tables in a LOT of queries it might be worth the effort to use synonymns to help maintain the code for you. A synonymn allows you to reference something under a different name.

关于同义词的更多信息。如果您打算在大量查询中使用这些链接服务器表，那么使用同义词来帮助您维护代码可能是值得的。同义词允许您引用不同名称的内容。

So for example when selecting data from a linked server you would generally use the following syntax to get the data:

因此，例如从链接服务器选择数据时，您通常会使用以下语法来获取数据：

SELECT *
FROM Linkedserver.database.schema.table

If you created a synonym for Linkedserver.database.schema.table as DBTable1 the syntax would be:

如果您将 Linkedserver.database.schema.table 的同义词创建为 DBTable1，则语法为：

SELECT *
FROM DBTable1

It saves a bit on typing plus if your linked server ever changed you would not need to go do changes all over your code. Like I said this can really be of benefit if you use linked servers in a lot of code.

它可以节省一点打字时间，而且如果您的链接服务器发生过更改，您就不需要对代码进行更改。就像我说的，如果您在大量代码中使用链接服务器，这真的很有用。

On a more cautionary note you CAN do a join between two table on different servers. HOwever this is normally painfully slow. I have found that you can select the data from the different server into temp tables and joining the temp tables can generally speed things up. Your milage might vary but if you are going to join the tables on the different servers this technique can help.

更要注意的是，您可以在不同服务器上的两个表之间进行连接。然而，这通常非常缓慢。我发现您可以将来自不同服务器的数据选择到临时表中，加入临时表通常可以加快速度。您的里程可能会有所不同，但如果您要加入不同服务器上的表，此技术会有所帮助。

Let me know if you need more details.

如果您需要更多详细信息，请告诉我。

回答by Satish

Which database are you using? Most of databases come with concept called dblinks. You have to create a dblink of database B in database A and then you can create a synonym (not a must but for ease) and use it as if it is table of database A.

您使用的是哪个数据库？大多数数据库都带有称为 dblinks 的概念。您必须在数据库 A 中创建数据库 B 的 dblink，然后您可以创建同义词（不是必须但为了方便）并将其用作数据库 A 的表。

回答by user2012230

Looks like a heterogeneous join (data on disparate servers/technologies etc).

看起来像是异构连接（不同服务器/技术等上的数据）。

As such, not straightforward. If you can make Namphibian's method work, go that way.

因此，并不直截了当。如果你能让 Namphibian 的方法奏效，那就去吧。

Otherwise, you need to gather the data from both tables to a common location (one or other of the servers 'in play', or a third server/technology solely for the purpose of co-locating the data). Then you can join the data happily. Many ETL Tools work this way, and this situation (almost) always involves redistribution of one or more of the tables to a common location before joining. Oracle Data Integrator ETL tool does this, so does Talend Open Studio's tJoin component.

否则，您需要将两个表中的数据收集到一个公共位置（一个或另一个“正在运行”的服务器，或仅用于共同定位数据的第三个服务器/技术）。然后就可以愉快的加入数据了。许多 ETL 工具以这种方式工作，并且这种情况（几乎）总是涉及在加入之前将一个或多个表重新分配到公共​​位置。Oracle Data Integrator ETL 工具执行此操作，Talend Open Studio 的 tJoin 组件也执行此操作。

HTH

HTH

回答by Luis H Cabrejo

While I was having trouble join those two tables, I got away with doing exactly what I wanted by opening both remote databases at the same time. MySQL 5.6 (php 7.1) and the other MySQL 5.1 (php 5.6)

当我在连接这两个表时遇到问题时，我通过同时打开两个远程数据库来做我想做的事情。MySQL 5.6 (php 7.1) 和其他 MySQL 5.1 (php 5.6)

//Open a new connection to the MySQL server
$mysqli1 = new mysqli('server1','user1','password1','database1');
$mysqli2 = new mysqli('server2','user2','password2','database2');

//Output any connection error
if ($mysqli1->connect_error) {
    die('Error : ('. $mysqli1->connect_errno .') '. $mysqli1->connect_error);
} else { 
echo "DB1 open OK<br>";
}
if ($mysqli2->connect_error) {
    die('Error : ('. $mysqli2->connect_errno .') '. $mysqli2->connect_error);
} else { 
echo "DB2 open OK<br><br>";
}

If you get those two OKs on screen, then both databases are open and ready. Then you can proceed to do your querys.

如果您在屏幕上看到这两个 OK，则两个数据库都已打开并准备就绪。然后，您可以继续进行查询。

On your specific question I will do something like first selecting from database A all the 10 year old kids then match them to the colors by the ID from database B. It should work, I havent tested this code on my server, but my sample below this code works. You can custom query by anything, color, age, whatever, even group them as you require to.

在你的具体问题上，我会做一些事情，比如首先从数据库 A 中选择所有 10 岁的孩子，然后通过数据库 B 中的 ID 将它们与颜色匹配。它应该可以工作，我还没有在我的服务器上测试过这段代码，但我的示例如下此代码有效。您可以按任何内容、颜色、年龄等自定义查询，甚至可以根据需要对它们进行分组。

$results = $mysqli1->query("SELECT * FROM DatabaseTableA where age=10");
    while($row = $results->fetch_array()) {
        $theColorID = $row[0];
        $theName = $row[1];
        $theAge = $row[2];
        echo "Kid Color ID : ".$theColorID." ".$theName." ".$theAge."<br>";
        $doSelectColor = $mysqli2->query("SELECT * FROM DatabaseTableB where favorite_color=".$theColorID." "); 
        while($row = $doSelectColor->fetch_assoc()) {
            echo "Kid Favorite Color : ".$row["favorite_color"]."<br>";
        }
    }

I have use this to switch back and forth for our programs without joining tables from remote servers and have no problem so far.

我使用它来回切换我们的程序，而无需从远程服务器加入表，到目前为止没有问题。

$results = $mysqli1->query("SELECT * FROM video where video_id_old is NULL");
    while($row = $results->fetch_array()) {
        $theID = $row[0];
        echo "Original ID : ".$theID." <br>";
        $doInsert = $mysqli2->query("INSERT INTO video (...) VALUES (...)");
        $doGetVideoID = $mysqli2->query("SELECT video_id, time_stamp from video where user_id = '".$row[13]."' and time_stamp = ".$row[28]." ");
            while($row = $doGetVideoID->fetch_assoc()) {
                echo "New video_id : ".$row["video_id"]." user_id : ".$row["user_id"]." time_stamp : ".$row["time_stamp"]."<br>";
                $sql = "UPDATE video SET video_id_old = video_id, video_id = ".$row["video_id"]." where user_id = '".$row["user_id"]."' and video_id = ".$theID.";";
                $sql .= "UPDATE video_audio SET video_id = ".$row["video_id"]." where video_id = ".$theID.";";
                // Execute multi query if you want
                if (mysqli_multi_query($mysqli1, $sql)) {
                    // Query successful do whatever...
                }
            }
    }
// close connection 
$mysqli1->close();
$mysqli2->close();

I was trying to do some joins but since I got those two DBs open, then I can go back and forth doing querys by just changing the connection $mysqli1or $mysqli2

我试图进行一些连接，但是由于我打开了这两个数据库，因此我可以通过更改连接来来回执行查询$mysqli1或$mysqli2

It worked for me, I hope it helps... Cheers

它对我有用，我希望它有帮助...干杯

回答by Rick Gittins

Try creating 3 Linq queries in Visual Studio. One for SQL Server, one for Oracle and one to combine the 2 database objects.

尝试在 Visual Studio 中创建 3 个 Linq 查询。一种用于 SQL Server，一种用于 Oracle，一种用于组合 2 个数据库对象。

回答by djangojazz

If you want to select data from two different servers and database I would do a union and not a join as the data from one may be like apples and the other may be like oranges. You still would need to set up linked Servers and I believe you may link Oracle and SQL Server if after certain versions as shown but you could do something like this:

如果你想从两个不同的服务器和数据库中选择数据，我会做一个联合而不是连接，因为来自一个的数据可能像苹果，另一个可能像橘子。您仍然需要设置链接服务器，我相信如果在所示的某些版本之后您可以链接 Oracle 和 SQL Server，但您可以执行以下操作：

select ColA, ColB, ColC
from (ServerASQLServer).(DatabaseA).(schema).(table)
UNION
select ColA, ColB, ColC
from (ServerBOracleServer).(DatabaseB).(schema).(table)

If you perform inner joins your data must share data types to bind to or else they will be ommitted from the dataset returned. A union must just shared column data types but does not care on the logic. You are in essence saying: "Put these two sets of varying rows together based on their column logic matching."

如果您执行内部联接，您的数据必须共享要绑定到的数据类型，否则它们将从返回的数据集中被忽略。联合必须只共享列数据类型，但不关心逻辑。您实质上是在说：“根据列逻辑匹配将这两组不同的行放在一起。”

But you were mentioning connection strings so I was curious if you would want to do it in a type of code method like .NET? I could provide an idea for that too possibly.

但是你提到了连接字符串，所以我很好奇你是否想用像 .NET 这样的代码方法来做它？我也可以为此提供一个想法。

回答by Brian Salta

SELECT (things)
FROM databaseA.dbo.table t1
INNER JOIN databaseB.dbo.table t2 ON t1.Col1 = t2.Col2
WHERE t1.Col1 = 'something'

EDIT - This statement should meet the new requirements:

编辑 - 此语句应满足新要求：

SELECT *
FROM databaseA.dbo.table t1
INNER JOIN databaseB.dbo.table t2 ON t1.ID = t2.ID
WHERE t1.Age = 10 AND t2.FavoriteColor = 'Blue'

回答by sgeddes

Assuming the databases are on the same server, you should be able to do something like this:

假设数据库在同一台服务器上，您应该能够执行以下操作：

SELECT t.field1, t.field2
FROM database.schema.table t
JOIN database2.scheme.table2 t2 
   on t.id = t2.id
WHERE t2.field3 = ...

If the databases are on separate servers, look into using Linked Servers.

如果数据库位于不同的服务器上，请考虑使用Linked Servers.

回答by bonCodigo

As long as both databases are in the same server you can refer to tables with the database name :)

只要两个数据库都在同一台服务器上，您就可以引用具有数据库名称的表:)

SELECT * FROM db1.table1
join 
db2.tbable2 
WHERE db1.table1.col1 = db2.table2.col1;