CHECKSUM Table Synchronization

Table Synchronization Demo using CHECKSUM

Prerequisites
1) Microsoft's Demo AdventureWorks database.
2) A second copy of AdventureWorks,, restored as AdventureSync

Step 1 : Create the usp_SyncTables procedure -

USE AdventureWorks
GO

CREATE PROCEDURE dbo.usp_SyncTables AS
/*

 
  dbo.usp_SyncTables
 
 
  Procedure to update Person table from AdventureWorks
 
 
  For each table perform 3 operations >
  1. Insert missing records
  2. Remove deleted records
  3. Update ammended records
 
 
  
   1
   20/05/2007
   SQL Solace
   Original Version
  
  
*/

-- sync tables in tsql
SET IDENTITY_INSERT [AdventureSync].[Person].[Contact] ON

INSERT INTO [AdventureSync].[Person].[Contact]
          ([ContactID]
     ,[NameStyle]
          ,[Title]
          ,[FirstName]
          ,[MiddleName]
          ,[LastName]
          ,[Suffix]
          ,[EmailAddress]
          ,[EmailPromotion]
          ,[Phone]
          ,[PasswordHash]
          ,[PasswordSalt]
          ,[rowguid]
          ,[ModifiedDate])
SELECT source.[ContactID]
     ,source.[NameStyle]
     ,source.[Title]
     ,source.[FirstName]
     ,source.[MiddleName]
     ,source.[LastName]
     ,source.[Suffix]
     ,source.[EmailAddress]
     ,source.[EmailPromotion]
     ,source.[Phone]
     ,source.[PasswordHash]
     ,source.[PasswordSalt]
     ,source.[rowguid]
     ,source.[ModifiedDate]
 FROM [AdventureWorks].[Person].[Contact] source
LEFT OUTER JOIN [AdventureSync].[Person].[Contact] target
ON source.[ContactID] = target.[ContactID]
WHERE target.[ContactID] is null

SET IDENTITY_INSERT [AdventureSync].[Person].[Contact] OFF

-- items to delete
DELETE [AdventureSync].[Person].[Contact]
FROM [AdventureWorks].[Person].[Contact] source 
RIGHT OUTER JOIN [AdventureSync].[Person].[Contact] target
ON source.[ContactID] = target.[ContactID]
WHERE source.[ContactID] is null

-- items to update
UPDATE [AdventureSync].[Person].[Contact]
  SET [NameStyle] = source.[NameStyle]
     ,[Title] = source.[Title]
     ,[FirstName] = source.[FirstName]
     ,[MiddleName] = source.[MiddleName]
     ,[LastName] = source.[LastName]
     ,[Suffix] = source.[Suffix]
     ,[EmailAddress] = source.[EmailAddress]
     ,[EmailPromotion] = source.[EmailPromotion]
     ,[Phone] = source.[Phone]
     ,[PasswordHash] = source.[PasswordHash]
     ,[PasswordSalt] = source.[PasswordSalt]
     ,[rowguid] = source.[rowguid]
     ,[ModifiedDate] = source.[ModifiedDate]
from [AdventureWorks].[Person].[Contact] source
join [AdventureSync].[Person].[Contact] target
on source.[ContactID] = target.[ContactID]
and CHECKSUM(source.[NameStyle]
   ,source.[Title]
   ,source.[FirstName]
   ,source.[MiddleName]
   ,source.[LastName]
   ,source.[Suffix]
   ,source.[EmailAddress]
   ,source.[EmailPromotion]
   ,source.[Phone]
   ,source.[PasswordHash]
   ,source.[PasswordSalt]
   ,source.[rowguid]
   ,source.[ModifiedDate]) <> CHECKSUM(target.[NameStyle]
            ,target.[Title]
            ,target.[FirstName]
            ,target.[MiddleName]
            ,target.[LastName]
            ,target.[Suffix]
            ,target.[EmailAddress]
            ,target.[EmailPromotion]
            ,target.[Phone]
            ,target.[PasswordHash]
            ,target.[PasswordSalt]
            ,target.[rowguid]
            ,target.[ModifiedDate])

GO

Step 2 : Create some work for the sync procedure by messing up the data in the AdventureSync database -

-- Insert an extra 3770 rows in AdventureSync by duping rows where EmailPromotion = 2
INSERT INTO [AdventureSync].[Person].[Contact]
          ([NameStyle]
          ,[Title]
          ,[FirstName]
          ,[MiddleName]
          ,[LastName]
          ,[Suffix]
          ,[EmailAddress]
          ,[EmailPromotion]
          ,[Phone]
          ,[PasswordHash]
          ,[PasswordSalt]
          ,[ModifiedDate])
SELECT [NameStyle]
     ,[Title]
     ,[FirstName]
     ,[MiddleName]
     ,[LastName]
     ,[Suffix]
     ,[EmailAddress]
     ,[EmailPromotion]
     ,[Phone]
     ,[PasswordHash]
     ,[PasswordSalt]
     ,[ModifiedDate]
 FROM [AdventureWorks].[Person].[Contact]
WHERE [EmailPromotion] = 2

-- Update 468 rows
UPDATE [AdventureSync].[Person].[Contact]
SET [LastName] = 'Fred'
WHERE [LastName] LIKE 'Z%'

GO

Step 3 : Test the procedure -

use AdventureWorks;

exec usp_SyncTables

The Output -

(0 row(s) affected)
(3770 row(s) affected)
(468 row(s) affected)

The output of the procedure shows -

0 rows inserted (correct, we didnt delete any)
3770 rows deleted (correct, these were the ones we inserted)
468 rows updated (correct, the LastName values have been corrected).