select * from A FULL JOIN B on a.ID=b.ID and NAME=NAME and NUMBER=NUMBER where a.id is null or b.id is null 2个表的ID都不为空的情况下,有数据就是不完全一致
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) 如果没有记录,说明完全相同
如果ID NAME 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/* 相同 */
FULL JOIN B on a.ID=b.ID and NAME=NAME and NUMBER=NUMBER
where a.id is null or b.id is null 2个表的ID都不为空的情况下,有数据就是不完全一致
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 '两表相同'
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 @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/*
相同
*/