如何判断2张表中的数据完全一致,谢谢了
两张表，结构一致, 如何知道两张表中的数据是否完全一致
表A结构
IDNAMENUMBER
表B结构
IDNAMENUMBER
[解决办法]
declare @a int
declare @b int
select checksum(ltrim(id)+ '- '+ltrim(name)+ '- '+ltrim(number)) AA from A
set @a=@@rowcount
select * from
(select checksum(ltrim(id)+ '- '+ltrim(name)+ '- '+ltrim(number)) AA from A) aaa
Inner Join
(select checksum(ltrim(id)+ '- '+ltrim(name)+ '- '+ltrim(number)) BB from B) bbb
On aaa.aa=bbb.bb
set @b=@@rowcount
if @a=@b
print '两表相同 '
[解决办法]
select * from tbla a
where not exists(select 1 from tblb b where a.id=b.id and a.name=b.name and a.number=b.number)
union all
select * from tblb a
where not exists(select 1 from tbla b where a.id=b.id and a.name=b.name and a.number=b.number)
如果没有记录，说明完全相同
[解决办法]
declare @t1 table(id int,name varchar(10))
declare @t2 table(id int,name varchar(10))
insert @t1
select 1, 'a ' union all
select 2, 'b ' union all
select 3, 'c ' union all
select 4, 'd '
insert @t2
select 1, 'a ' union all
select 2, 'b ' union all
select 3, 'c ' union all
select 4, 'd '
----使用checksum时表中不能含有text、ntext、image类型的列
select case
when exists(select 1 from @t1 where checksum(*) not in(select checksum(*) from @t2))
then '不相同 '
else '相同 ' end
/*
相同
*/
