SQL Script for Comparing the Contents of Two Tables

Wednesday Aug 7th 2002 by DatabaseJournal.com Staff
Share:

Eli Leiba presents a T-SQL script that will allow you to compare two tables to determine if their content is the same.


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 T-SQL 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


Share:
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved