Comparing two data sets field by field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Comparing two data sets field by field

Author

Topic

trader1012
Starting Member

1 Post

Posted - 2009-09-24 : 11:53:27

I want to compare two tables daily. Each table has 10 columns...and about 2000 rows - trades (which can differ daily). My query can compare both the table with the help of Primary key, means the query will return any additional trade from one of the tables...but it does not return if two fields are different...for example let's say if cell G2 in Table 1 is different from cell G2 in Table 2...here is my query

MQ_SEQ_NO is the primary key.....which is basically trade ID and must be same for both the tables..Table 1 and Table 2

declare @START_DATE DATETIME, @END_DATE DATETIME

SELECT @START_DATE = '09/23/2009'
select @END_dATE = '09/24/2009'

SELECT 'Table1'
, [MQ_SEQ_NO]
,[TRANS_SEQ_NO]
,[FIRM]
,[JJDDD]
,[ORIG_REF]
,[XCL_REV_CD]
,[OFFSET_CD]
,[USER_REF]
,[CXLD_COMBINED_REF]
,[STREET]

FROM Table1
where CONVERT(DATETIME,create_dt) >= @START_DATE and CONVERT(DATETIME,create_dt) < @END_DATE
AND NOT EXISTS (

SELECT
[MQ_SEQ_NO]
,[TRANS_SEQ_NO]
,[FIRM]
,[JJDDD]
,[ORIG_REF]
,[XCL_REV_CD]
,[OFFSET_CD]
,[USER_REF]
,[CXLD_COMBINED_REF]
,[STREET]

FROM Table2
where CONVERT(DATETIME,create_dt) >= @START_DATE and CONVERT(DATETIME,create_dt) < @END_DATE
AND Table1.[MQ_SEQ_NO] = Table2.[MQ_SEQ_NO] )

UNION

SELECT 'Table2' ,
[MQ_SEQ_NO]
,[TRANS_SEQ_NO]
,[FIRM]
,[JJDDD]
,[ORIG_REF]
,[XCL_REV_CD]
,[OFFSET_CD]
,[USER_REF]
,[CXLD_COMBINED_REF]
,[STREET]

FROM Table2
where CONVERT(DATETIME,create_dt) >= @START_DATE and CONVERT(DATETIME,create_dt) < @END_DATE
AND NOT EXISTS (
SELECT [MQ_SEQ_NO]
,[TRANS_SEQ_NO]
,[FIRM]
,[JJDDD]
,[ORIG_REF]
,[XCL_REV_CD]
,[OFFSET_CD]
,[USER_REF]
,[CXLD_COMBINED_REF]
,[STREET]

FROM Table1
where CONVERT(DATETIME,create_dt) >= @START_DATE and CONVERT(DATETIME,create_dt) < @END_DATE
AND Table2.[MQ_SEQ_NO] = Table1.[MQ_SEQ_NO])

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-24 : 14:02:48

Here are a couple techniques to show differences between similar tables:


declare @t1 table (pk int primary key clustered, v1 int, v2 int)
declare @t2 table (pk int primary key clustered, v1 int, v2 int)

set nocount on

insert @t1
select 1,1,1 union all
select 2,1,1 union all
select 3,1,1 union all
select 5,1,1

insert @t2
select 1,1,1 union all
select 2,10,1 union all
select 3,1,1 union all
select 4,1,1

print 'all rows in @t1 that don''t exist in @t2'
select t1.*
from   @t1 t1
left outer join @t2 t2
       on t2.pk = t1.pk
where  t2.pk is null

print 'all rows in @t2 that don''t exist in @t1'
select t2.*
from   @t2 t2
left outer join @t1 t1
       on t1.pk = t2.pk
where  t1.pk is null

print 'Show all differences'
--If the PK is repeated that means that there is a difference in at least one of the columns.
--If the PK appears only once then that row exists only in the table: "source"
select min(src) as Source
       ,d.pk
       ,d.v1
       ,d.v2
from   (
       select src = 't1', pk,v1,v2 from @t1
       union all
       select src = 't2', pk,v1,v2 from @t2
       ) as d
group by d.pk
       ,d.v1
       ,d.v2
having count(*) = 1
order by d.pk
       ,min(src)


OUTPUT:
all rows in @t1 that don't exist in @t2
pk          v1          v2
----------- ----------- -----------
5           1           1

all rows in @t2 that don't exist in @t1
pk          v1          v2
----------- ----------- -----------
4           1           1

Show all differences
Source pk          v1          v2
------ ----------- ----------- -----------
t1     2           1           1
t2     2           10          1
t2     4           1           1
t1     5           1           1

Be One with the Optimizer
TG

Dance Doll
Yak Posting Veteran

54 Posts

Posted - 2009-09-24 : 15:46:32

You also can download tablediff.exe.
This is the cool tool to compare 2 tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources