Seeking advice to determine a good option for table row comparisons

Question

Seeking advice to determine a good option for table row comparisons

vsamantha35

SSChampion

Points: 11529
More actions
July 18, 2018 at 12:04 am

#407203

Hi All,
We have a requirement where we need to compare table rows and merge the rows. Based on my research on internet found below options.
Need some advise regarding which ones is a better option and what are the drawbacks of other remaining options. Would like to know pros and cons in terms of additional overhead, simplicity/maintainability, performance etc...
1. Merge statement
2. CDC
3. create a custom change log table
4. checksum
5. hash bytes
Thanks,
Sam

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 1

there are many ways to achieve what you are asking as you can see from your research, before anyone could help we would require some more information around the process.

can you provide the full process, what exactly are you wanting to achieve, how often you will be looking to do the compare, what are you comparing against, why is this needed etc

***The first step is always the hardest *******

ScottPletcher SSC Guru Points: 100949 More actions · Answer 2

Use MS's free tool "tablediff.exe" and let them do this work for you, and fully tested already! Unfortunately quirky to get working initially, but works well after that, and it's free.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 3

vsamantha35 - Wednesday, July 18, 2018 12:04 AM
Hi All,
We have a requirement where we need to compare table rows and merge the rows. Based on my research on internet found below options.
Need some advise regarding which ones is a better option and what are the drawbacks of other remaining options. Would like to know pros and cons in terms of additional overhead, simplicity/maintainability, performance etc...
1. Merge statement
2. CDC
3. create a custom change log table
4. checksum
5. hash bytes
Thanks,
Sam

What do items 2 and 3 have to do with merging data?

Also, if you're trying to do whole row comparisons, you could also use EXCEPT, which would could be used to create a dataset of differences.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)