SQL 2000: using checksum function to compare tables

There is collision problem.  The function HashBytes may be an alternative.

SELECT ISNULL(n.id, o.id) id
, CASE
   WHEN n.id IS NULL THEN 'deleted'
   WHEN o.id IS NULL THEN 'inserted'
   ELSE 'updated'
   END flag

    FROM (
    SELECT id
, CHECKSUM(*) row_checksum
    FROM table1
) n
    FULL 
    OUTER JOIN
(
    SELECT id
, CHECKSUM(*) row_checksum
    FROM table2
) o
    ON n.id = o.id

    WHERE n.id IS NULL
   OR o.id IS NULL
   OR n.row_checksum <> o.row_checksum

http://blogs.clarience.com/davide/?p=11
http://msdn.microsoft.com/en-us/library/ms189788(SQL.90).aspx
http://technet.microsoft.com/en-us/library/ms174415(v=sql.90).aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s