Posted by Alexander Kuznetsov on 10/15/36 11:29

if the table has a PK, there is no need to do tedios column-by-colmn
comparisons:

create table t1(i int identity, j int)
insert into t1(j) values(1)
insert into t1(j) values(2)
insert into t1(j) values(3)

create table t2(i int identity, j int)
insert into t2(j) values(1)
insert into t2(j) values(2)
insert into t2(j) values(4)

------ rows in t1 that do not have exact match in t2
select * from t1 t
---- there is a row in t2 with the same PK
where exists(select * from t2 where t2.i = t.i)
---- but some other columns are different
and
(select count(*) from
(
select * from t1
union
select * from t2
)t_both where t_both.i = t.i
) = 2


------ rows in t2 that do not have exact match in t1
select * from t2 t
---- there is a row in t1 with the same PK
where exists(select * from t1 where t1.i = t.i)
---- but some other columns are different
and
(select count(*) from
(
select * from t1
union
select * from t2
)t_both where t_both.i = t.i
) = 2

drop table t1
drop table t2

[Back to original message]