如果列名相同，那么从两个不同的数据库中的两个表中找出差异？ [英] Find the difference from two table in two different database, if Column name is same ?

问题描述

从[Database1] .dbo.Table1中选择*其中Value not in（从[DataBase2] .dbo.Table1中选择值）

//这里：

DB1 ，DB2-Database

同一个表名同时存在Table1，在两个数据库中。

和Value是列名。

select * from [Database1].dbo.Table1 where Value not in(select Value from [DataBase2].dbo.Table1)
//Here:
DB1,DB2-Database
same table name present Table1, in both Database.
and Value is the column Name.

推荐答案

select Value11 as Value1,  Value22 as Value2 , 'different' as status from
(
select tbl1.Value as Value11 , tbl2.Value as Value12
from
[DB1].dbo.Table1 tbl1
left outer join [DB2].dbo.Table1 tbl2
on tbl1.Value = tbl2.Value
)first,
(
select tbl1.Value as Value21 , tbl2.Value as Value22
from
[DB1].dbo.Table1 tbl1
right outer join [DB2].dbo.Table1 tbl2
on tbl1.Value = tbl2.Value
) second

where first.Value12 is null and second.Value21 is null

如果您使用的是SQL SERVER 2005及更高版本您可以使用除外 ata比较：



请考虑以下代码：

If you are using SQL SERVER 2005 and above then you can use EXCEPT for data comparison:

Consider the following code:

Declare @tblA table ( ID int, Name varchar(20))
Declare @tblA_Copy table ( ID int, Name varchar(20))

insert into @tblA
select 1, 'ABC' union all
select 2, 'ABC1' union all
select 3, 'ABC2' union all
select 4, 'ABC3' 

insert into @tblA_Copy
select 11, 'ABC' union all
select 12, 'ABC13' union all
select 13, 'ABC23' union all
select 14, 'ABC33' 

--- all the columns difference From tblA to tblA_Copy
select * from @tblA
except 
select * from @tblA_Copy
--- all the columns difference From tblA_Copy to tblA
select * from @tblA_Copy
except 
select * from @tblA
--- Single columns difference From tblA to tblA_Copy
select Name from @tblA
except 
select Name from @tblA_Copy
--- Single columns difference From tblA_Copy to tblA
select Name from @tblA_Copy
except 
select Name from @tblA

希望它有所帮助。

Hope it helps.

这篇关于如果列名相同，那么从两个不同的数据库中的两个表中找出差异？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！