Using BINARY_CHECKSUM (*) with multiple tables

Question

I am trying to do a very hacked change tracking using an outdated application.

What I plan to do is store the BINARY_CHECKSUM values ​​of each row in a separate table so that it can be compared in my application.

Imagine that I have 2 tables - TableA, TableB
Both have a ProductID column (So can join two tables together)

I am looking for a request:

SELECT a.ProductID, a.BINARY_CHECKSUM(*)AS Hash1, b.BINARY_CHECKSUM(*) AS Hash2 FROM TableA a JOIN TableB b ON a.ProductID = b.ProductID 

This obviously does not work ... but something along these lines, so the result will be (for example)

ProductID | Hash1 | hash2
1234 | --439419708 | -35860977

Answer 1

This might work:

 SELECT a.ProductID, Hash1, Hash2 FROM (select ProductID, BINARY_CHECKSUM(*) AS Hash1 from TableA a) a JOIN (select ProductID, BINARY_CHECKSUM(*) AS Hash1 from TableB b) b on ... 

Your problem is just a syntax problem. Your approach will work.

Answer 2

you can try something:

 with a as ( select BINARY_CHECKSUM(*) as CheckSum, * from TableA ), b as ( select BINARY_CHECKSUM(*) as CheckSum, * from TableB ) select a.keyField, a.CheckSum, b.CheckSum from a full outer join b on a.keyField = b.keyField where a.CheckSum <> b.CheckSum