MS SQL Server 5 - Incremental or Differential Loading in SQL Server

Hello All,

Myself chakrapani Upadhyaya, in this article we will learn how to Incremental Load in SQL server.

What is Incremental Load?

An incremental load is a process where the new data is inserted from some source to destination when there is no data match from source and destination, whereas source data is updated when there is a match. 

In the incremental load, we find out how many are the once which can be updated to destination table and how many records are inserted from source table to destination table, we just update or insert to the destination table

Why Incremental Load?

Suppose if the table is very large, for example there are millions of records to load, so is not possible to load large amount of data in a very small time because some time we have very small duration so we can just update the data.

And one more great advantage is? it help us to store the history of data(mean when it is inserted and deleted), where as full load will first delete all records and insert again, so its difficult to maintain the history.

How can we achieve Incremental Load in SQL Server?

We us MERGE operation to achieve the Incremental Load.

What is MERGE Operation?

Basically MERGE Operation is technique, where merges data from a source to a target table based on a condition that you specify and if the data from the source already exists in the target or not. We run INSERT, UPDATE and DELETE commands in a single atomic statement, depending on the existence of a record. 

SYNTAX...

MERGE target_table USING source_table  
ON merge_condition  
WHEN MATCHED  
    THEN update_statement  
WHEN NOT MATCHED  
    THEN insert_statement  
WHEN NOT MATCHED BY SOURCE  
    THEN DELETE;  

STEP BY STEP DEMONSTRATION:

Lets work on simple example...

1. Create new database EmpManagerDB

create database EmpManagerDB

2. Use/open created database

use EmpManagerDB

3. Next we will create two table with same number of columns and datatypes

Lets create employees and employee_stg table

create table employee_stg
(
	empid		int primary key,
	fullname	varchar(100),
	emailId		varchar(100),
	status		varchar(10),
	createDt        Datetime
)

create table employee
(
	empid		int primary key,
	fullname	varchar(100),
	emailId		varchar(100),
	status		varchar(10),
	createDt        Datetime,
	updateDt	Datetime
)

4. Lets insert some records to the table employee_stg table

insert into employee_stg values(1,'test user','testuser1@test.com',

'active',getdate())
insert into employee_stg values(2,'test user1','testuser2@test.com',

'active',getdate())
insert into employee_stg values(3,'test user2','testuser3@test.com',

'active',getdate())
insert into employee_stg values(4,'test user3','testuser4@test.com',

'active',getdate())
insert into employee_stg values(5,'test user4','testuser5@test.com',

'active',getdate())
insert into employee_stg values(6,'test user5','testuser6@test.com',

'active',getdate())

lets view the result

5. Its time to implement the incremental load operation.

We need to move the data from employee_stg table to employee table

Agenda....

• Insert records when data present in source table and not in target table
• Update records when data present in source table and in target table
• Delete records when data present in target table but not in source

select * from employeeThis will help us to keep both table in sync.

and also create and update captured based on insert and update.

MERGE employee AS tgt
USING employee_stg AS src
	ON tgt.empid=src.empid
WHEN MATCHED
THEN UPDATE
	SET tgt.fullName = src.fullname,
		tgt.status = src.status,
		tgt.updateDt = GETDATE()
WHEN NOT MATCHED BY TARGET
THEN INSERT 
	(empid,fullname,emailid,status,createDt,updateDt)
	VALUES
	(src.empid,src.fullname,src.emailid,src.status,GETDATE(),NULL)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;

Note : MERGE Operation should end with semicolon(;).

Lets view the data from employee table

6. Lets insert one more record to employee_stg table and update some records in it

insert into employee_stg values(7,'test user7','testuser1@test.com7',

'active',getdate())

and

update employee_stg set status='in-active' where empid=5

finally we data in employee_stg table is

Lets run merge operation again, then we should see new records and updated records in employee table

Create Date and Update Date should be inserted properly.

We should not have updated date for newly created record.

That's it for this article, i hope you enjoyed.

In case any concerns... Please let us know by comment section

See you in next article, until then take care bye...