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]

#MAX-VALUE# 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 #MAX-VALUE# is replaced with an actual value before the execution. (Use global variables to avoid syntax errors)

Transformer

Just passes values to data writer

Writer 1

Adds records

Variables

Set Flag

Updates records in source table