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
 Data Comparision

Author  Topic 

deepugun
Starting Member

11 Posts

Posted - 2014-07-22 : 15:43:22
Hello All,
I have a monthly quarterly feed coming from an external app and i intend to load that data into an table . I strongly believe that we would have data quality issues with the data we get i.e. the data we get this quarter might differ from data what we get in next quarter(Its a full feed every time).Fields such as DOB of a customer should never change but we have seen the values change for this field every three months. I would want to flag this. So i am trying to come up with a script to do so.I will be having data from the old quater in a table and the new data in a other table.I will now have to find which rows have changed from whole field.I would like to identify precisely which column has changed. I think i can use hashbytes or full join to do so but was also wondering if i could get to know any gotchas or suggestions .
some of the issues where i can foresee myself struggling are :
1) Identfying the newly added rows fromt he old feed and the new feed. I would like them to not be part of comparision as those new customers who might have got added. I would like to comparision on older records which exist on both but some how some of their columns have changed.
2) Handling Text field comparision

Thanks

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-07-22 : 18:46:20
I'm not quite sure of your requirements but I'll list a few things to consider:
1) MERGE operator might be useful to perform the insert/update of the data
2) EXCEPT operator would be useful to find which existing rows have actually changed (e.g.[CODE]select o.*
from OldData o
inner join
NewData n
on n.MyKey = o.MyKey
except
select *
from NewData[/CODE] (Or something like that...)



Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-24 : 12:40:21
You can use the OUTPUT operator to capture which rows were inserted and which were updated.


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -