How to do incremental loading in SQL Server

Question

I have two tables product_source (with 50 records) and product_target (no records).

create table product_source
(
     productId varchar(10),
     productName varchar(50),
     productStatus varchar(20),
     productType varchar(20),
     productDesc varchar(50)
)

create table product_target
(
     productId varchar(10),
     productName varchar(50),
     productStatus varchar(20),
     productType varchar(20),
     productDesc varchar(50)
)

Sample data in product_source table:

productId productName   productStatus productType productDesc   
-------   ----------     -----------  -----------  -----------  
111          lenova       pending      computer   hp_computer   
222          pendrive     delivered    gadget     storage
777          harddisk     pending      storage    storagedevice
...50 records

I loaded the data into target table using insert statement for example.

insert into product_target as 
    select * 
    from product_source     -- 50 records loaded

product_target table:

productId productName   productStatus productType productDesc    
-------   ----------     -----------  -----------  -----------   
111          lenova       pending      computer   hp_computer   
222          pendrive     delivered    gadget     storage
...50 records got loaded

Now again some data (for example 100 new records) got loaded into source table product_source, now how to load only those 100 new records into product_target table with column is_status stating whether any new record inserted/updated in source then it should add as below.

product_source table (with the new records):

productId productName   productStatus productType productDesc   
-------   ----------     -----------  -----------  -----------  
111          samsung       delivered    mobile     sam_mobile     
989          scandisk     inprogress    pendrive   pendrive_64gb
546          mouse        inprogress    computer   mouse_computer
666          keyboard     inprogress    computer   keyboard_computer
777          harddisk     delivered     storage    storagedevice
...100 records

product_target table:

productId productName   productStatus productType productDesc    Is_status
-------   ----------     -----------  -----------  -----------   ----------
555          lenova       pending       computer   hp_computer       inserted
111          lenova       delivered     computer   hp_computer       updated  
777          harddisk     delivered     storage    storagedevice     updated
989          scandisk     inprogress    pendrive   pendrive_64gb     inserted
546          mouse        inprogress    computer   mouse_computer    inserted
666          keyboard     inprogress    computer   keyboard_computer inserted
...

Similarly if load happens every day in product_source table, then how to load every new inserted/updated recorded in product_target table?

I used merge statement in loading from product_source to product_target...but no getting. Could anyone look into this?

Answer 1

You can do this by two ways, 1. In SSIS, By using SCD transformation or Lookup Transformation you can do incremental Loading. 2. second way is by writing Merge statement in Sql server we can do Incremental Loading.