This article was created to assist our customers with data synchronization.
In our examples we will be synchronizing between different physical SQL servers, the same methodology can be used for any database
Here is table creation script
CREATE TABLE TBL_SOURCE ( PRIMARY_KEY int NOT NULL IDENTITY (1,1), DATA varchar(50) NULL, UPDATE_FLAG varchar(5) NULL ) ON [PRIMARY] GO ALTER TABLE TBL_SOURCE ADD CONSTRAINT PK_TBL_SOURCE PRIMARY KEY CLUSTERED (PRIMARY_KEY) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE TABLE TBL_TARGET (PRIMARY_KEY int NOT NULL, DATA varchar(50) NULL, UPDATE_FLAG varchar(5) NULL ) ON [PRIMARY] GO ALTER TABLE TBL_TARGET ADD CONSTRAINT PK_TBL_TARGET PRIMARY KEY CLUSTERED (PRIMARY_KEY) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
Reads new records from the source table using the following SQL
Select TBL_SOURCE.PRIMARY_KEY, TBL_SOURCE.DATA, TBL_SOURCE.UPDATE_FLAG From TBL_SOURCE Where TBL_SOURCE.UPDATE_FLAG = 'N'
Adds new records only
for every incoming record runs the following SQL
select count(*) as rec_count from target table where PRIMARY_KEY=?
if rec_count is zero new records is inserted into the target table
Once loading is completed the following SQL is executed to mark processed records in the target table.
update TBL_TARGET SET UPDATE_FLAG='Y' WHERE UPDATE_FLAG='N'
Using this approach allow easy identification of newly inserted records in case of failure
Sorter is used to delay passing records to writer 2 until writer 1 is completed
Updates records in the source table
update TBL_SOURCE SET UPDATE_FLAG = 'Y' where PRIMARY_KEY=?