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
Objective

Load new records only into the target table
Once loading is completed source records must be marked as updated

Assumptions

Data is only inserted into the source table
There are no changes to the source records
There are no deletions to the source records

Implementation

Synchronization1

Reader

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'

Transformer 1

Synchronization1 Transformer1

Writer 1

Adds new records only

Synchronization1 Writer1

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

Synchronization1 UdateKey1

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 to easily identify newly inserted records in case of failure

Synchronization1 Writer11

Sorter

The sorter is used to delay passing records to writer 2 until writer 1 is completed

Synchronization1 Sorter

Transformer 2Synchronization1 Transformer2
Writer 2

Updates records in the source table

update TBL_SOURCE
SET    UPDATE_FLAG = 'Y'
where  PRIMARY_KEY=?

Synchronization1 Writer2

Download Free Trial Learn More About Our ETL Software
 
Xerox
Swiss banking
Bank Of Oklahoma
Red Cross
Alta Pacific bank
Copeinca
Gas alberta
NHS
Royal Brunei
First Oklahoma bank
Noresco
Iqvia

Testimonials

What customers say about us

  • swissbanking

    I used Advanced ETL Processor in 2 Enterprises for many business processes and Business automation (outside finance department). I did not find any other tool with so many functions and broad flexibility for that Price! If you need support for bugs or solution design you will get it very fast. Best Support I have ever seen.

    Lionel Albrecht
  • iqvia

    IQVIA and DB Software Laboratory (DBSL) partnered in 2010 and have been working in close cooperation ever since. Over this period of time DBSL software components formed an integral part of a large number of IQVIA applications currently used by over 20 UK NHS Trusts (Hospitals).

    Dmitry Dorsky,
    Director
  • xerox

    The product is easy to learn and once a developer understands the ETL way for solving the problem at hand, the developer's productivity will increase. Even our DBAs now uses the ETL software to quickly create solutions instead of SSIS or .SQL jobs.

    Daniel Fung
    Solutions Architect

 

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