Easy way to compare the contents of 2 tables ?

25 Aug 2006 9:34 AM

Rob

I've got a couple tables with identical structure...

I would like to create an exception report indicating any differences in the
content of any of the columns (even in the case where one value may be null
and its counterpart a space)... Any ideas on how to build such a query ?

25 Aug 2006 10:07 AM

ML

First of all, both tables should have a key - one or more columns that
uniquely identify each row (preferably columns that cannot be modified), in
order to be able to match corresponding rows in both tables.

Then the query is fairly simple:

select <column list>
         from <table one>
                 inner join <table two>
                               on <table one>.<key> = <table two>.<key>
         where (<table one>.<column1> != <table two>.<column1>)
                   and (<table one>.<column2> != <table two>.<column2>)
                   ...
                   and (<table one>.<columnN> != <table two>.<columnN>)

For a more accurate answer post DDL and sample data.

ML

---
http://milambda.blogspot.com/

25 Aug 2006 1:02 PM

Alexander Kuznetsov

Rob,

create table #a1(i int primary key, c char(1))
insert into #a1 values(1, 'A')
insert into #a1 values(2, 'B')
insert into #a1 values(3, 'C')
insert into #a1 values(5, null)
go
create table #a2(i int primary key, c char(1))
insert into #a2 values(1, 'A')
insert into #a2 values(2, 'U')
insert into #a2 values(4, 'V')
insert into #a2 values(5, null)
go
--- rows in only one table
select #a1.i, #a2.i
from #a1 full outer join #a2 on #a1.i = #a2.i
where #a1.i is null or #a2.i is null

/*

i           i
----------- -----------
3           NULL
NULL        4

(2 row(s) affected)
*/

-- different rows
-- instead of comparing all the nullable columns
-- as follows
-- #a1.c = #a2.c
-- or #a1.c is null and #a2.c is not null
-- or #a2.c is null and #a1.c is not null
-- just let UNION do it for you
select i from(
select #a1.* from #a1 join #a2 on #a1.i = #a2.i
union
select #a2.* from #a2 join #a1 on #a1.i = #a2.i
) t
group by i
having count(*)>1

i
-----------
2

(1 row(s) affected)

26 Aug 2006 12:35 PM

Rob

Thanks... I like your last suggestion...
- Rob

Show quote

"Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message
news:1156510960.525617.279530@h48g2000cwc.googlegroups.com...
> Rob,
>
> create table #a1(i int primary key, c char(1))
> insert into #a1 values(1, 'A')
> insert into #a1 values(2, 'B')
> insert into #a1 values(3, 'C')
> insert into #a1 values(5, null)
> go
> create table #a2(i int primary key, c char(1))
> insert into #a2 values(1, 'A')
> insert into #a2 values(2, 'U')
> insert into #a2 values(4, 'V')
> insert into #a2 values(5, null)
> go
> --- rows in only one table
> select #a1.i, #a2.i
> from #a1 full outer join #a2 on #a1.i = #a2.i
> where #a1.i is null or #a2.i is null
>
> /*
>
> i           i
> ----------- -----------
> 3           NULL
> NULL        4
>
> (2 row(s) affected)
> */
>
> -- different rows
> -- instead of comparing all the nullable columns
> -- as follows
> -- #a1.c = #a2.c
> -- or #a1.c is null and #a2.c is not null
> -- or #a2.c is null and #a1.c is not null
> -- just let UNION do it for you
> select i from(
> select #a1.* from #a1 join #a2 on #a1.i = #a2.i
> union
> select #a2.* from #a2 join #a1 on #a1.i = #a2.i
> ) t
> group by i
> having count(*)>1
>
> i
> -----------
> 2
>
> (1 row(s) affected)
>

26 Aug 2006 12:45 PM

--CELKO--

>> I've got a couple tables with identical structure...<<

That should not happen. A table should contain all the entities of the
same kind in one and only one table.

>> I would like to create an exception report indicating any differences in the content of any of the columns (even in the case where one value may be null and its counterpart a space)... Any ideas on how to build such a query ? <<

If you have SQL-2005

(SELECT * FROM Foo
EXCEPT
SELECT * FROM Bar)
UNION
(SELECT * FROM Bar
EXCEPT
SELECT * FROM Foo)

This does not require that you know the structure of the tables, just
that they are alike.
This is called a OUTER UNION (not the same as an OUTER JOIN!) and is
defined in the SQL-92 Standards. Nobody implements it.