SQL 通过连接不同服务器上两个数据库中的两个表来查询数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5145637/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Querying data by joining two tables in two database on different servers
提问by Kashif
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?
在不同服务器上的两个不同数据库中有两个表，我需要加入它们以便进行少量查询。我有哪些选择？我该怎么办？
采纳答案by Scott Anderson
You'll need to use sp_addlinkedserverto create a server link. See the reference documentationfor 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来创建服务器链接。有关用法，请参阅参考文档。建立服务器链接后，您将像往常一样构造查询，只需在数据库名称前加上另一台服务器即可。IE：
-- 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 OPENQUERYto 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 DB1in 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 OPENQUERYto 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 OPENQUERYcan save some time and performance if you use the query to filter out some data.
查看OPENQUERY的文档以查看更多示例。上面的例子非常做作。在这个特定示例中，我肯定会使用第一种方法，但是OPENQUERY如果您使用查询过滤掉一些数据，则使用第二种方法可以节省一些时间和性能。
回答by Dev ashish and kapil jangid
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
回答by HLGEM
If a linked server is not allowed by your dba, you can use OPENROWSET. Books Online will provide the syntax you need.
如果您的 dba 不允许链接服务器，您可以使用 OPENROWSET。联机丛书将提供您需要的语法。
回答by Dave
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.
从实际的企业角度来看，最佳实践是在您的数据库中制作数据库表的镜像副本，然后让一个任务/进程每小时使用增量更新它。
回答by Abdulmoeed
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 服务器上执行此操作，但我想如果表很大，则不建议这样做。
回答by Holdfast
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 报告之类的内容，并在那里进行连接。
回答by Niklas Henricson
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：分别从每个数据库收集数据并将其加入您的代码中。您的数据库连接字符串可以通过数据库或配置文件成为应用服务器配置的一部分。
回答by Bha15
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的地方，你必须定义数据库的名称。如果您在同一个数据库中，则不需要定义数据库名称，但如果您在其他数据库中，则必须提及数据库名称作为路径，否则会显示错误。希望我让你的工作变得轻松
回答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，则两个数据库都已打开并准备就绪。然后，您可以继续进行查询。
$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 sohan yadav
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
