2

We are trying to compare the values of whole tables between many databases. The user can enter in input the table names and the columns names, and the database names he wants to compare.

They can enter as many databases, tables and columns they wish. We want to compare rows per rows, only for the specified columns.

ex:
DatabaseA, DatabaseB, DatabaseC
Table1, Col1|Col2|Col3|Col4
Table2, Col1|Col4|Col5|Col6|Col20
...

So for an example, if I have:

DatabaseA
Table1, Col1|Col2|Col3|Col4 = 'Apple', 1, 10, 'ABC'

DatabaseB
Table1, Col1|Col2|Col3|Col4 = 'Banana', 1, 10, 'ABC'

There's a difference.

At first I was considering usign the CHECKSUM on each rows if each tables (excluding columns that where not specified by the user) and compare the CHECKSUM but I've read that it's not always unique.

Now I was thinking of using HASHBYTES instead. Either by doing this:

    SELECT HASHBYTES('sha2_512', CONVERT(NVARCHAR(MAX), ISNULL(col1,'')) +
           HASHBYTES('sha2_512', CONVERT(NVARCHAR(MAX), ISNULL(col2,'')) +
           HASHBYTES('sha2_512', CONVERT(NVARCHAR(MAX), ISNULL(col3,'')) +
           HASHBYTES('sha2_512', CONVERT(NVARCHAR(MAX), ISNULL(col4,'')) +
    FROM Table1

Or by doing this:

   With Vals AS 
   (
       SELECT CONVERT(NVARCHAR(MAX), ISNULL(col1, '')) + 
              CONVERT(NVARCHAR(MAX), ISNULL(col2, '')) + 
              CONVERT(NVARCHAR(MAX), ISNULL(col3, '')) + 
              CONVERT(NVARCHAR(MAX), ISNULL(col4, ''))  AS Val
       FROM Table1
   )
   SELECT HASHBYTES ('sha2_512', Val) FROM Vals

What do you think? How would you do it? What would be the best way?

Thanks

  • 1
    You should know that gashbytes will only hash the first 8000 bytes. – Mikael Eriksson Mar 5 '15 at 19:30
  • Thanks! I haven't though of that. Wich means that hashing every column individually then adding them up is probably a better way to avoid the limit. – Danielle Paquette-Harvey Mar 5 '15 at 19:38
  • Or I stick to checksum... don't know wich one is best – Danielle Paquette-Harvey Mar 5 '15 at 20:22
  • What do you want the result to show? Match/no match? Different table structures, column data types? How much detail? – Andrew Wolfe Mar 5 '15 at 21:39
  • I want the result to show Match/no match on data. (Not table structure or data types) – Danielle Paquette-Harvey Mar 6 '15 at 13:53
3

This may not be exactly what you are looking to do, but you could use the EXCEPT statement to get the rows that are different:

SELECT Column1, Column2 FROM DatabaseName1.dbo.TableName1
EXCEPT
SELECT Column1, Column2 FROM DatabaseName2.dbo.TableName2

If you do it this way though, make sure you also do the opposite EXCEPT so you know the rows that don't exist in the other database's table:

SELECT Column1, Column2 FROM DatabaseName2.dbo.TableName2
EXCEPT
SELECT Column1, Column2 FROM DatabaseName1.dbo.TableName1

Hopefully that helps you a little.

1

Finally we have decided that the best way to do it is with the checksum. The reason is: we want to compare rows per rows for the specified columns (wich checksum lets you specify).

At first we wanted to go with hashbytes but as Mikael told me, it has a limit of 8000 bytes, and that is really too small.

And although Adam's solution is a good one, it is not exactly what we want to do.

So our solution is:

With CheckTables AS 
(
SELECT 'Database1' as Name, 'Table1' as TableName, checksum( col1, col2, col3, col4) as chk, col1, col2, col3, col4 FROM Database1.dbo.Table1
UNION       all
SELECT 'Database2' as Name, 'Table2' as TableName, checksum( col1, col2, col3, col4) as chk, col1, col2, col3, col4 FROM Database2.dbo.Table1
-- All of the databases/tables/cols here
),
SameCheckSum AS
(
select Name, TableName, count(chk) over (partition by tablename, chk) as SameTableChck, col1, col2, col3, col4
from CheckTables
group by BdSrc,tablename, chk,  col1, col2, col3, col4
)
SELECT *
FROM SameCheckSum
where SameTableChck = 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.