create schema authorization sa
create table #t1 (
i1 int , c1 char( 1 ) , vc1 varchar( 10 )
)
create table #t2 (
i2 int , c2 char( 1 ) , vc2 varchar( 10 )
)
go

insert into #t1 values( 1 , '1' , '1111' );
insert into #t1 values( 11 , 'a' , 'aaaa' );
insert into #t2 values( 1 , '1' , '1111' );
insert into #t2 values( 2 , '2' , '2222' );
go
-- Orphans in #t1 only
select i1 , c1 , vc1 from 
(
select * , checksum( * ) as chksum1 from #t1
) as t1
where not exists ( select *  from (
                        select * , checksum( * ) as chksum2 from #t2
                   ) as t2
                   where  chksum1 = chksum2
        );

-- Orphans in #t2 only
select i2 , c2 , vc2 from 
(
select * , checksum( * ) as chksum2 from #t2
) as t2
where not exists ( select *  from (
                        select * , checksum( * ) as chksum1 from #t1
                   ) as t1
                   where  chksum1 = chksum2
        )


-- Matching rows in #t1 & #t2
select i1 , c1 , vc1 from 
(
select * , checksum( * ) as chksum1 from #t1
) as t1
where exists ( select *  from (
                        select * , checksum( * ) as chksum2 from #t2
                   ) as t2
                   where  chksum1 = chksum2
        );
go

-- You can simplify this further if you define a computed column for
-- the checksum like:
alter table #t1 Add chksum as ( checksum( i1 , c1 , vc1 ) );
alter table #t2 Add chksum as ( checksum( i2 , c2 , vc2 ) );
go
-- Orphans in #t1 only
select i1 , c1 , vc1 from #t1 as t1
where not exists ( select *  from #t2 as t2
                   where t1.chksum = t2.chksum
        );

-- Orphans in #t2 only
select i2 , c2 , vc2 from #t2 as t2
where not exists ( select *  from #t1 as t1
                   where t1.chksum = t2.chksum
        )


-- Matching rows in #t1 & #t2
select i1 , c1 , vc1 from #t1 as t1
where exists ( select *  from #t2 as t2
                   where t1.chksum = t2.chksum
        );
go
drop table #t2;
drop table #t1;
go

This page was last updated on May 01, 2006 04:28 PM.