How To Do Incremental Loading In Sql Server

I have two tables product_source (with 50 records) and product_target (no records). create table product_source ( productId varchar(10), productName varchar(50), pr

Solution 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.

Solution 2:

You could output your "100 new records" you are updating/inserting to product_source into temp tables.

Then join the temp tables to the product_target table for inserting/updating.

http://www.sqlservercentral.com/articles/T-SQL/156204/

Solution 3:

Assuming the primary key on the table is productId

insertinto product_target (
 productId,
 productName,
 productStatus,
 productType,
 productDesc
 )
    select 
 productId,
 productName,
 productStatus,
 productType,
 productDesc
    from product_source SRC
    WHERENOTEXISTS (SELECT*FROM product_target TGT 
                      WHERE TGT.productID = SRC.ProductId);

Note: you should always use column lists when inserting and selecting like this.

In my opinion:

Don't use a temp table to store the things you inserted. This is redundant and over complicated since you already know whats in the target table so why go store it in another separate table

I suggest that you don't use SSIS to do it. Where source and target tables are in the same database, a T-SQL solution is often best, unless you have wider requirements (like logging, configuration, complex scheduling, lots of tables etc.). The SCD component is inefficient for large data sets (it's fine for a few thousand records but don't get into the habit of using it). Also the SCD component is a 'destructive wizard' - it deletes and recreates the flows every time. The Lookup component is also more ineffecient that T-SQL.