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
The following SQL is used to get a starting point for the data reader, table PK_STATISTICS is used to store values.
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
Does actual loading
Reads new records from the source table using the following SQL
Variable 1234567890 is replaced with an actual value before the execution
Just passes values to data writer
Adds new records
SQL executed before the writer starts loading records
SQL executed once the writer finishes loading records
Here we assign values to 1111111111111 and 9999999999 variables
Updates records in source table