mssql checksum on different tables

Question

I need to find if two rows (one having the same id of the other +50000) are the same. Is there any way to make this query work?

select 1 
from table1 c1, 
     table2 c2 
where c1.id=c2.id+50000 and CHECKSUM(c1.*) = CHECKSUM(c2.*)

CHECKSUM() apparently does not accept "table.*" expressions. It accepts either "*" alone or list of columns, but I can't do that as this query needs to work also for other tables with other columns.

EDIT: I just realized that CHECKSUM() will not work as the value will always be different if the IDs are different....

The original question still holds out of curiosity.

CHECKSUM(*) will include the id column too in the checksum, so the 2 row checksums will differ even if all the columns except id are the same. So you can't use CHECKSUM() and have to compare every column (except id) explicitly. — Cosmin Rus
– Cosmin Rus, Commented Feb 12, 2016 at 14:02

t-clausen.dk · Accepted Answer · 2016-02-12 14:00:09Z

1

Try something like this, it will work for most datatypes (not TEXT and some others):

SELECT 1 
FROM
  table1 c1
JOIN 
  table2 c2 
ON 
  c1.id=c2.id+50000 and
  EXISTS(SELECT c1.col1, col2, col3, col4 EXCEPT SELECT c2.col1, col2, col3, col4)

edited Feb 12, 2016 at 14:00

answered Feb 12, 2016 at 13:58

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Evaldas Buinauskas Over a year ago

You could CAST TEXT as VARCHAR.

Tab Alleman · Accepted Answer · 2016-02-12 14:07:53Z

You can do it using derived tables:

    SELECT 
            SUM(CASE
                WHEN a.cs <> b.cs THEN 1
                ELSE 0
            END)
        FROM (SELECT RowNumber, CHECKSUM(*) AS cs FROM #A) a
        FULL OUTER JOIN (SELECT RowNumber, CHECKSUM(*) AS cs FROM #B) b
            ON a.RowNumber=b.RowNumber;

This is an excerpt from a script I've written previously. I have not changed any of the object names to match your example. The result of this query is the number of differences between #A and #B where the RowNumber columns match.

To apply to your need, you can create two temporary tables, populating them from the originals, but replacing the ID column with a "RowNumber" column that matches between the rows you want to match (ie: c1.id=c2.id+50000). That way, you don't have mis-matched IDs to interfere with the CHECKSUM.

Collectives™ on Stack Overflow

mssql checksum on different tables

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related