SQL Script for Comparing the Contents of Two Tables
August 7, 2002
Here's some SQL code from Eli Leiba
that will all you to compare two tables  say, table A and table B  to determine if their content is the same. Assuming that A and B have the same structure, here's how it works:
First, from set theory, recall that:
If ((A = B) && A U B = A)) ====>>> A = B
A = NUMBER of rows in A
B = NUMBER of rows in B
Here's the SQL code (with TSQL syntax, but can be adapted for other DBMS's):
declare @cnt1 int
declare @cnt2 int
declare @cnt3 int
declare @res bit
select @cnt1 = count(*) from A
select @cnt2 = count(*) from B
select @cnt3 = count('x')
from (select * from A
UNION
select * from B) as t
if (@cnt1 = @cnt2) and (@cnt2 = @cnt3)
begin
set @res = 1
print 'A = B'
end
else
begin
set @res = 0
print 'A <> B'
end
go
»
See All Articles by Columnist Eli Leiba
