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.
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 data2) EXCEPT operator would be useful to find which existing rows have actually changed (e.g.[CODE]select o.*from OldData oinner join NewData n on n.MyKey = o.MyKeyexceptselect *from NewData[/CODE] (Or something like that...) Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy |
|
|
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 |
|
|
|
|
|