Use of Merge Command: Update if changed, Insert when not exists, Delete if not found in Target (Incremental Load)/ UPSERT Command

Hello All,

Today we will be looking how to achieve the Incremental Load concept which is in SSIS, through SQL.

SQL provides us MERGE command to accomplish this task.

We shall start off with creating two tables: 

1. Source
2. Destination

Create table Src (ID Int, Name varchar(100), Designation Varchar(100))

Insert Into Src

Select 1, 'Dhina', 'Snr.Analyst'

Union

Select 2, 'Scott', 'Lead Analyst'

Union

Select 5, 'Peter', 'Jnr. Analyst'

Create table Dest (ID Int, Name varchar(100), Designation Varchar(100))

Insert Into Dest

Select 1, 'Dhina', 'Analyst'

Union

Select 2, 'Scott', 'Lead Analyst'

Union

Select 3, 'Brad', 'Test Analyst'

Select * From Src

Select * From Dest

Go

will result as shown in snippet:

As Shown, We need to Update employee 'Dhina' who promoted to Snr.Analyst from Analyst in Destination.

We need to Insert a new guy 'Peter' in destination table as it is not present in destination table "Dest".

and we shall also delete "Brad" whose record is found in destination table "Dest" but not in source table "Src".

In this way, we can maintain, the Source table and Destination table synchronized.

We have two types of loads: 

• Full Load
• Incremental Load.

Full Load: Dropping or truncating the Entire dataset and load new value from the Source. This is not recommended when we are having huge amount of records and also when we have dependencies on the destination table.

This method though it is simple but takes lot of time and Non Availability of the Destination/target table at any given point of time.

Incremental Load: This method works on only few records which has changed or added recently. Hence the old records, are untouched and since it is working on few record sets, this method is faster and will not spoil or hamper any dependencies.

Incremental load can be created using SSIS which I would explain in a separate blog. Here we shall concentrate on SQL on how to achieve the same.

merge  [dbo].[dest] as d

       using  [dbo].Src as s

       on d.id=s.id

when matched

       then update set d.designation=s.designation

when  not matched

       then insert (id,name,designation)

       values (s.id,s.name,s.designation)

when  not matched by source

Split wise explanation:

We can see the Merge command has to be followed by Destination Table.

Using must be followed by Source Table. 

When Records are matched based on the ID's, then Update the record if any changes happened.

When records are not matched between source and destination, then it means, its a new record from the source and is not present in destination. Hence use Insert Statement.

When Records are not matching with SOURCE table, then We can delete those records.

The result of the above Upsert Statement is shown in below snippet and we can say, the synchronization is maintained.