data compare between 2 tables with same structure for any changes after etl run

Question

data compare between 2 tables with same structure for any changes after etl run

syam.tunuguntla

SSC Veteran

Points: 226
More actions
April 22, 2026 at 2:03 pm

#4776280

i have huge table with lot of data and is also wide. i took a backup of it before procedure is run in test environment. now i am trying to see only using SQL what changed and finally what i am expecting is how many inserts, updates or deletes are there. i have some idea of using EXCEPT in SQL but tables being large and wide i hesitate to run it
April 25, 2026 at 6:06 am

This was removed by the editor as SPAM
April 25, 2026 at 2:33 pm

This was removed by the editor as SPAM
May 1, 2026 at 6:17 am

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 10 (of 10 total)

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

ScottPletcher SSC Guru Points: 101245 More actions · Answer 1

Hmm, it would have been best to put a data change capture in place before the mods occurred. If the mods aren't too long and you can go back to a truly accurate backup, that might be a good choice.

Barring that, about all you can do is exclude what parts of the table you can (e.g. really old data) and compare the rest, with EXCEPT or whatever.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

SQL BrainBox SSC Veteran Points: 249 More actions · Answer 2

Hopefully you got a create and modify date fields on that table.

Create field for inserts and modify field for updates.

For deletes, if the primary key is unique and sequential without any losses before sproc execution, then search for missing keys.

Tung Dang
Azure and SQL Server DBA Contractor / Consultant
SQL Brainbox - SQL Server Monitoring Tool

RobertThomas Grasshopper Points: 15 More actions · Answer 3

RobertThomas

Grasshopper

Points: 15

April 23, 2026 at 9:58 am

#4777011

Thank you, I will keep it in mind.

deepeshdhake Say Hey Kid Points: 674 More actions · Answer 4

Change Tracking or Change Data Capture is good option for on-going monitoring.

Deepesh Dhake
Database Administrator

Jeff Moden SSC Guru Points: 1004721 More actions · Answer 5

syam.tunuguntla wrote:

i have huge table with lot of data and is also wide. i took a backup of it before procedure is run in test environment. now i am trying to see only using SQL what changed and finally what i am expecting is how many inserts, updates or deletes are there. i have some idea of using EXCEPT in SQL but tables being large and wide i hesitate to run it

I have to ask the "obvious" question.. do you have a Primary Key constraint or, at least, a unique index on the table? If so, what is/are the column(s)?

And what are you describing as "huge"? What is the rowcount of the table and what is the average row width or number of rows per page? (You can find that out using sys.dm_db_index_physical_stats() on Index ID 0 or 1.

--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)

Massimo Preitano SSC Veteran Points: 206 More actions · Answer 6

I think your idea can work well to identify inserts and deletes, regardless of how large the tables are.

The key condition is that you have a stable business key (not only an incremental identity column). In that case, the EXCEPT comparison can be limited to the key columns rather than the whole table:

currentTable EXCEPT backupTable to identify inserts;
backupTable EXCEPT currentTable for deletes.

Your concerns remain valid when it comes to identifying updates.