如何比较来自2个独立数据库的表 [英] How do I compare tables from 2 separate databases

问题描述

所以我一直在使用一个SQL语句，它允许我使用同一个数据库中的表进行比较。这是代码：

使用[table]作为T 
合并到table1作为T 
在T. [姓氏] = S. [姓氏]和T. [名字] = S. [名字] 
 
匹配时
更新集T. [Companynamne] = S. [公司名]; 
 
 DELETE T1 FROM [table] T1 JOIN [table1] T2 ON T1。[姓氏] = T2。[姓氏]和T1。[名字] = T2。[名字]; 

我试图使用相同的代码来比较另一个数据库中的另一个表，我不断得到一个out of object错误。



我想使用相同的代码但是经过修改后，我需要更改以从第二个数据库中获取信息，如SQL公式中的S和T1作为同一件事的删除声明？



我尝试了什么：



我无法弄清楚如何更改它以识别它是用于比较来自2个数据库的2个表。

解决方案

只需添加数据库的名称表名前面 - 例如：

 [database1]。[dbo]。[table_A] 

和

 [database2]。[dbo]。[table_B] 

如果数据库位于两个不同的服务器中，只需以相同的方式添加SQL服务器实例名称：

 [Server1]。[ database1]。[dbo]。[table_A] 

和

 [Server2]。[database2]。 [DBO]。[表-B] 

So I have been using an SQL statement that will allow me to use tables from the same database for comparison. Here is the code:

Merge into table1 as T
using [table] as S
on T.[Last Name] = S.[Last Name] and T.[First Name] = S.[First Name]

When Matched then 
Update Set T.[Companynamne] = S.[Companyname];

DELETE T1 FROM [table] T1 JOIN [table1] T2 ON T1.[Last Name] = T2.[Last Name] AND T1.[First Name] = T2.[First name];

I tried to use the same code to compare another table from another database, I kept getting an out of object error.

I would like to use this same code but with modifications, what would I need to change to get the information from the second database as S in the SQL formula and T1 as the delete statement for the same thing?

What I have tried:

I couldn't figure out how to change it to recognize that it's for comparing 2 tables from 2 databases.

解决方案

Simply add the name of the databases in front of the table name - example:

[database1].[dbo].[table_A]

and

[database2].[dbo].[table_B]

If the databases are in two different servers, just add the SQL server instance name the same way:

[Server1].[database1].[dbo].[table_A]

and

[Server2].[database2].[dbo].[table_B]

这篇关于如何比较来自2个独立数据库的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！