如何比较两张内容差不多的表？

[ywduan]

为了保证输入的准确性，现在有两人往两种同样结构的表中输入同样的数据，假设每个人输入的主键字段都正确，最后应该如何审核两张表的其他字段是否相同？

[ywduan]

SQL版的人真热心啊
谢谢

[pengdali]

差：
c1-c2:

select * from t1 where not exists(select 1 from t2 where t1.c1=t2.c1 and t1.c2=t2.c2)

c2-c1:

select * from t2 where not exists(select 1 from t1 where t1.c1=t2.c1 and t1.c2=t2.c2)

[txlicenhe]

Select * from A
join B on a.key = b.key
where a.F1 <> b.F1 or a.F2 <> b.F2 or a.F3 <> b.F3 ...

[ywduan]

非常感谢
都不错 我试试成功后结贴

[zjcxc]

逐个比较,

Select * from A a inner join B b on a.key = b.key
where a.F1 <> b.F1 or a.F2 <> b.F2 or a.F3 <> b.F3 ...其他字段逐个比较

[zjcxc]

如果不想手工写字段,可以用生成SQL语句的方法:

declare @sql varchar(8000)
set @sql='select Select * from A a inner join B b on a.key = b.key where '
select @sql=@sql+'a.['+name+']<>b.['+name+'] or '
from syscolumns where object_id('A')=id and name<>'key'

set @sql=left(@sql,len(@sql)-4)
exec(@sql)