In the previous article, 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

Here is a practical example

Synchronization3 Package

 

Start value

The following SQL is used to get a starting point for 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

Synchronization3 S1

Synchronization3 Package Variables1

Synchronization3 Package Variables2

 

Transformation S3

Does actual loading

Synchronization3

 

Reader

Reads new records from the source table using the following SQL

Variable 1234567890  is replaced with an actual value before the execution

Synchronization3 Datareader1

Transformer

Just passes values to datawriter

Synchronization1 Transformer1

Writer 1

Adds new records

Synchronization2 Writer1

Sql executed before the writer starts loading records

Synchronization3 Writer11

SQL executed once the writer finishes loading records

Synchronization3 Writer12

Variables

Here we assign values to 1111111111111 and 9999999999 variables

Synchronization3 S2

Set Flag

Updates records in the source tableSynchronization3 SetFlag

Variables values

 

Synchronization3 Package Variables

Direct link, no registration required.
 

This site uses cookies. By continuing to browse the site, you are agreeing to our use of cookies