如何在两个表中只找到不匹配的数据列 [英] 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屋!
查看全文