如何在两个表中只找到不匹配的数据列 [英] how to find only mismatched data columns in two tables

问题描述

您好我在同一个数据库中有两个表。这两个表有相同的列数和数据。但是如何在两个表中只找到不匹配的数据列数据。









提前致谢

Hi I have two tables in the same database .both the tables have same number columns and data.but how can I find only mismatched data columns data in both the tables.




Thanks in advance

推荐答案

declare @mtbname1 as varchar(50)
declare @mtbname2 as varchar(50)


set @mtbname1  = 'tablename1'
set @mtbname2  = 'tablename2'



select * from (select tb.name , colum.name col  from sys.tables tb
inner join 
sys.all_columns colum on 
tb.object_id = colum.object_id  
where tb.name = @mtbname1 )tb1
left outer join   
(select tb.name , colum.name col from sys.tables tb
inner join 
sys.all_columns colum on 
tb.object_id = colum.object_id  
where tb.name = @mtbname2)tb2 
on tb1.col = tb2.col 
where tb2.col is null 

union 


select * from (select tb.name , colum.name col  from sys.tables tb
inner join 
sys.all_columns colum on 
tb.object_id = colum.object_id  
where tb.name = @mtbname1 )tb1
right outer join   
(select tb.name , colum.name col from sys.tables tb
inner join 
sys.all_columns colum on 
tb.object_id = colum.object_id  
where tb.name = @mtbname2)tb2 
on tb1.col = tb2.col 
where tb1.col is null

select * from table1除外（select * from table2）

Union

select * from table2 except（select *来自table1）

select * from table1 except (select * from table2)
Union
select * from table2 except (select * from table1)

这篇关于如何在两个表中只找到不匹配的数据列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！