Incremental loads and updates

Incremental load is the process where data is loaded from source to target table incrementally. Suppose Source table and target table both has 5 records and a new record inserted into the source table. Now from the source table need to load only the new record into target table. It means the target table should be inserted only with the new record. Similarly we also has incremental update, where update happens only to changed record but not all records.

This eliminates the burden of truncating all the records and loading it once again. This incremental process is made possible by using LEFT JOIN based on Primary key column.

Consider we have a table by name “TgtTableEmp” and source for this table is “SrcTableEmp” now the table TgtTableEmp should be inserted only with the new records from SrcTableEmp. So now there are 4 records in the SrcTableEmp and no records in TgtTableEmp.

CREATE TABLE dbo.SrcTableEmp

(

      EmpID VARCHAR(5),

      EmpName VARCHAR(40),

      EmpDept VARCHAR(10),

      EmpLoc VARCHAR(40),

      EmpSal MONEY

)

INSERT INTO dbo.SrcTableEmp VALUES('E001','AAA','HR','Delhi',20000)

INSERT INTO dbo.SrcTableEmp VALUES('E002','BBB','IT','Bangalore',30000)

INSERT INTO dbo.SrcTableEmp VALUES('E003','CCC','IT','Delhi',15000)

INSERT INTO dbo.SrcTableEmp VALUES('E004','AAA','HR','Bangalore',10000)

SELECT * FROM dbo.SrcTableEmp

CREATE TABLE dbo.TgtTableEmp

(

      EmpID VARCHAR(5),

      EmpName VARCHAR(40),

      EmpDept VARCHAR(10),

      EmpLoc VARCHAR(40),

      EmpSal MONEY

)

SELECT * FROM dbo.TgtTableEmp

Incremental Insert :

SELECT *

FROM dbo.SrcTableEmp s

LEFT JOIN dbo.TgtTableEmp t ON s.EmpID=t.EmpID

SELECT s.EmpID,s.EmpName,s.EmpDept,s.EmpLoc,s.EmpSal

FROM dbo.SrcTableEmp s

LEFT JOIN dbo.TgtTableEmp t ON s.EmpID=t.EmpID

WHERE t.EmpID IS NULL

INSERT INTO TgtTableEmp

(EmpID,EmpName,EmpDept,EmpLoc,EmpSal )

SELECT s.EmpID,s.EmpName,s.EmpDept,s.EmpLoc,s.EmpSal

FROM dbo.SrcTableEmp s

LEFT JOIN dbo.TgtTableEmp t ON s.EmpID=t.EmpID

WHERE t.EmpID IS NULL

SELECT * FROM dbo.TgtTableEmp

INSERT INTO dbo.SrcTableEmp VALUES('E005','GGG','FIN','Hydrabad',25000)

INSERT INTO dbo.SrcTableEmp VALUES('E006','YYY','FIN','Hydrabad',26000)

INSERT INTO dbo.SrcTableEmp VALUES('E007','SSS','IT','Goa',40000)

SELECT *

FROM dbo.SrcTableEmp s

LEFT JOIN dbo.TgtTableEmp t ON s.EmpID=t.EmpID 

SELECT s.EmpID,s.EmpName,s.EmpDept,s.EmpLoc,s.EmpSal

FROM dbo.SrcTableEmp s

LEFT JOIN dbo.TgtTableEmp t ON s.EmpID=t.EmpID

WHERE t.EmpID IS NULL

INSERT INTO TgtTableEmp

(EmpID,EmpName,EmpDept,EmpLoc,EmpSal )

SELECT s.EmpID,s.EmpName,s.EmpDept,s.EmpLoc,s.EmpSal

FROM dbo.SrcTableEmp s

LEFT JOIN dbo.TgtTableEmp t ON s.EmpID=t.EmpID

WHERE t.EmpID IS NULL

SELECT * FROM dbo.TgtTableEmp

SELECT *

FROM dbo.SrcTableEmp s

LEFT JOIN dbo.TgtTableEmp t ON s.EmpID=t.EmpID

SELECT s.EmpID,s.EmpName,s.EmpDept,s.EmpLoc,s.EmpSal

FROM dbo.SrcTableEmp s

LEFT JOIN dbo.TgtTableEmp t ON s.EmpID=t.EmpID

WHERE t.EmpID IS NULL

Incremental update METHOD 1:

SELECT * FROM dbo.SrcTableEmp

UPDATE Src

SET EmpLoc='Punjab'

FROM dbo.SrcTableEmp Src

WHERE EmpID='E007'

UPDATE Src

SET EmpDept='HR'

FROM dbo.SrcTableEmp Src

WHERE EmpID='E005'

SELECT * FROM dbo.SrcTableEmp

SELECT * FROM dbo.TgtTableEmp

SELECT *   

FROM dbo.TgtTableEmp tgt

JOIN dbo.SrcTableEmp src ON tgt.EmpID=src.EmpID

WHERE tgt.EmpName<>src.EmpName OR

      tgt.EmpDept<>src.EmpDept OR

      tgt.EmpLoc<>src.EmpLoc OR

      tgt.EmpSal<>src.EmpSal

UPDATE tgt

SET EmpName=src.EmpName,

    EmpDept=src.EmpDept,

    EmpLoc=src.EmpLoc,

    EmpSal=src.EmpSal

FROM dbo.TgtTableEmp tgt

JOIN dbo.SrcTableEmp src ON tgt.EmpID=src.EmpID

WHERE tgt.EmpName<>src.EmpName OR

      tgt.EmpDept<>src.EmpDept OR

      tgt.EmpLoc<>src.EmpLoc OR

      tgt.EmpSal<>src.EmpSal

SELECT * FROM dbo.TgtTableEmp

Incremental update METHOD 2:

UPDATE Src

SET EmpLoc='sikkim'

FROM dbo.SrcTableEmp Src

WHERE EmpID='E007'

UPDATE Src

SET EmpDept='FIN'

FROM dbo.SrcTableEmp Src

WHERE EmpID='E005'

SELECT * FROM dbo.SrcTableEmp

SELECT * FROM dbo.TgtTableEmp

SELECT EmpID,

       EmpName,

       EmpDept,

       EmpLoc,

       EmpSal 

FROM dbo.SrcTableEmp      

UNION

SELECT EmpID,

       EmpName,

       EmpDept,

       EmpLoc,

       EmpSal 

FROM dbo.TgtTableEmp

SELECT DISTINCT X.EmpID

FROM (SELECT EmpID,

         EmpName,

       EmpDept,

       EmpLoc,

       EmpSal 

FROM dbo.SrcTableEmp      

UNION

SELECT EmpID,

         EmpName,

       EmpDept,

       EmpLoc,

       EmpSal 

FROM dbo.TgtTableEmp) X

GROUP BY X.EmpID

HAVING COUNT(X.EmpID)>1

SELECT DISTINCT X.EmpID

INTO #temp

FROM (SELECT EmpID,

         EmpName,

       EmpDept,

       EmpLoc,

       EmpSal 

FROM dbo.SrcTableEmp      

UNION

SELECT EmpID,

         EmpName,

       EmpDept,

       EmpLoc,

       EmpSal 

FROM dbo.TgtTableEmp) X

GROUP BY X.EmpID

HAVING COUNT(X.EmpID)>1

SELECT * FROM #temp

SELECT *   

FROM dbo.TgtTableEmp tgt

JOIN dbo.SrcTableEmp src ON tgt.EmpID=src.EmpID

JOIN #temp tmp ON tmp.EmpID=src.EmpID

UPDATE tgt

SET EmpName=src.EmpName,

    EmpDept=src.EmpDept,

    EmpLoc=src.EmpLoc,

    EmpSal=src.EmpSal

FROM dbo.TgtTableEmp tgt

JOIN dbo.SrcTableEmp src ON tgt.EmpID=src.EmpID

JOIN #temp tmp ON tmp.EmpID=src.EmpID

SELECT * FROM dbo.TgtTableEmp