Data Synchronization part 3

In Data Synchronization Part 2 we demonstrated how easy it is to synchronize the data between different databases.

Both of the methods suggested works very well for small and medium-sized tables

For large tables, we must use a different approach. The basic idea is very simple we must work only with the relevant data.

In order to achieve that we must store and use maximum values of the primary key before and after the transformation

Example

Start value

The following SQL is used to get a starting point for the data reader, table PK_STATISTICS is used to store values.

Table creation script

TABLE [dbo].[PK_STATISTICS]
([TABLE_NAME] [varchar](50) NULL,
[PK_BEFORE] [int] NOT NULL,
[PK_AFTER] [int] NOT NULL
)
ON [PRIMARY]

1234567890 is a variable name, later inside data writer variable is replaced with the actual value

Variable Name can be any combination of characters

Transformation

Does actual loading

Reader

Reads new records from the source table using the following SQL

Variable 1234567890 is replaced with an actual value before the execution

Transformer

Just passes values to data writer

Writer 1

Adds new records

SQL executed before the writer starts loading records

SQL executed once the writer finishes loading records

Variables

Here we assign values to 1111111111111 and 9999999999 variables

Set Flag

Updates records in source table

Variables values