Data Validation across 2 systems

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

SQL Server 2005 Forums

Transact-SQL (2005)

Data Validation across 2 systems

Author

Topic

msv
Starting Member

9 Posts

Posted - 2009-05-25 : 22:42:12

I have a migration going on.. And there is data 4m the old system which is transferred into a new system, so currently doing data validation across the system.

For example a simple table that i need to verify between 2 databases... I need to make sure that the data is transferred correctly between DB_A n DB_B for the tables X of DB_A n Y of DB_B. In this case I am dealing with a customer table with orders.. A customer might have a lot of orders... So i need to check for Customer Mary who place 10 orders n the record is in DB_A(the original database)... i need to check if there is the same customer with 10 records in DB_B.

Once after the check i need to display the customers that werent copied... n also i shud display the customers whose order numbers are wrong as well.. if Mary has only 9 order recorded in DB_B i need to display that....

I am so confused in how to go about this.. I am able to pull the data

SELECT a.SRID as ID,
b.SR_NUM as Number,
count (*) as Total
FROM ACT a
with (nolock)
INNER JOIN SERV b (nolock) on a.SRID = b.SR_ID
GROUP BY a.SRID, b.SR_NUM

This results the data for one table but i need to cross check.. how do i do that????

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-26 : 00:47:39

I take it the databases are on the same server?
Easiest to work with temp tables

SELECT a.SRID as ID,
b.SR_NUM as Number,
count (*) as Total
into #new
FROM ACT a
with (nolock)
INNER JOIN SERV b (nolock)
on a.SRID = b.SR_ID
GROUP BY a.SRID, b.SR_NUM

SELECT a.SRID as ID,
b.SR_NUM as Number,
count (*) as Total
into #old
FROM olddb..ACT a
with (nolock)
INNER JOIN olddb..SERV b (nolock)
on a.SRID = b.SR_ID
GROUP BY a.SRID, b.SR_NUM

create index ix on #new (ID, Number) incude (Total)
create index ix on #old (ID, Number) incude (Total)

Then do the compare

select *
from #old o
full outer join #new n
on o.ID = n.ID
and o.Number = n.Number
and o.Total = n.Total
where o.ID is null or n.ID is null
or o.Total <> n.Total

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources