CODEWITHSUNDEEP
sqlsql-server
JohnM
JohnM

Reputation: 41

Find out the columns which got changed when comparing two tables in sql server

I have a table TableA with 10 columns having around 4000 rows. After done some calculations there were some changes in the table. To get those changes I first took the back up before calculations and then after calculations. So now I have 2 tables TableA_backup1 and TableA_backup2. Now I need to know which columns specifically changed in the rows using the above 2 tables.

Any help?

Upvotes: 0

Views: 1429

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522254

If you just want a "matrix" of rows and columns, with a 0/1 or maybe true/false in each cell, indicating whether a column changed, then you may simply join the two tables by primary key:

SELECT
    CASE WHEN t1.col1 = t2.col1 THEN 0 ELSE 1 END AS col1,
    CASE WHEN t1.col2 = t2.col2 THEN 0 ELSE 1 END AS col2,
    ...      -- other columns
FROM TableA_backup1 t1
INNER JOIN TableA_backup2 t2
    ON t1.pk = t2.pk
ORDER BY
    t1.pk;

This doesn't take into account the possibility that rows may have been lost or added in going from one backup snapshot to the other. But, in this case, if two rows don't match at all, we can't really do a comparison of the column values anyway.

Upvotes: 1

Related Questions