Data World

SQL Server-Check Data difference between two tables

Posted by Pramod Singla on November 25, 2015


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)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: