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).
