SQL Server-Check Data difference between two tables

Problem: How to compare tables and/or data to identify data changes between them?

Solution: In data migration, we often have to compare two tables and identify a record in one table that have no record in the corresponding table. The steps for comparing tables are as follows:-

  • Create two tables with similar structure:

use tempdb
go
/**Create source table*/
if exists (select 1 from sys.objects where name=’Source’)
drop table Source
create table Source (a int,c int)
/**Create target table*/
if exists (select 1 from sys.objects where name=’Target’)
drop table Target
create table Target (a int,c int)

  • Insert some data into both source and target tables:

/*Insert dummy records*/
insert into Source values(1,1)
insert into Source values(2,2)
insert into Source values(3,3)
insert into Source values(4,4)

insert into Target values(1,1)
insert into Target values(2,2)
insert into Target values(8,4)
insert into Target values(5,5)

  • Compare data of both the tables:

/**Compare data of two tables***/
SELECT ‘Type’=CASE
WHEN a.c IS NULL THEN ‘I’
WHEN b.c IS NULL THEN ‘D’
ELSE ‘U’
END,
a.a AS olA_Value_inSource,
b.a AS colA_Value_inTarget,
a.c AS colC_Value_inSource,
b.c AS colC_Value_inTarget
FROM Source A
FULL JOIN Target B
ON ( A.C = B.C )
WHERE A.C IS NULL
OR B.C IS NULL
OR Checksum(a.a, a.c) <> Checksum(b.a, b.c)

Leave a Reply

Your email address will not be published. Required fields are marked *