I have multiple tables in one database that I use to compare to an offline access database and see if this offline access database matches any of these predetermined tables. The tables are all identical in structure but contain different values; I'll make it simple by only utilizing one column "Setpoint".
I was using this snippet of code:
USE OvenGroups
EXECUTE sp_MSforeachtable '
Insert Into #TempChecksum (CheckSumResults, ProfileName)
Select CHECKSUM_AGG(Checksum([Setpoint]), SUBSTRING("[?]", 10, len("[?]")-12)
From ?
With (NOLOCK)
'
To create a checksum of the tables I have in my database and then compare them to each other. This was working great until I compared two tables that had totally different values in [Setpoint] and got the same checksum back:
Table1:Table2
170:120
170:120
180:135
180:135
200:155
200:155
220:235
220:235
255:255
255:255
180:220
180:220
(The rest of the two tables share identical values)
Results:
CheckSum Table Name
1130627072 6Z13A
1130627072 TestTest
Now I dont really understand how those two completely different datasets would return a different checksum but I am now using a CHECKSUM_AGG(Cast(HASHBYTES()as int) to get my checksum and I am comming up with unique values:
USE OvenGroups
EXECUTE sp_MSforeachtable '
--minusovbious has everything execpt the previous setpoint vaule
Insert Into #TempChecksum (CheckSumResults, ProfileName)
Select CHECKSUM_AGG(Cast(HASHBYTES(''SHA2_256'', CONCAT_WS(''|'', [Setpoint], (all my other columns), SUBSTRING("[?]", 10, len("[?]")-12)
From ?
With (NOLOCK)
'
Results:
CheckSum Table Name
-631830728 TestTest
-1375519242 6Z13A
Is this an appropriate solution? Will this return different results for tables? Am I missing something? Is there a better way to compare multiple columns in a table to other tables?
