Incremental delta load

Asif 376 Reputation points
2021-03-29T15:07:06.787+00:00

I have scenario, where I need to load incremental data in target table (2016Std edition SQL Server).
We get weekly incremental data in source table and the data in the source table get added for a week and the process updates/inserts the data for entire month along with adding any additional records for current week

I have to do insert or update(based on new or old data) the target table for entire month for every weeks incremental data in source. This can be done using MERGE and the batchID, which is common in target table for entire month.

I can do merge using batchid, but when I come across a scenario where the months last weeks incremental data falls into two batchid's(month ending and new month starting), I need to identify and pick two batchid(one for previous month and other for current) in this case. This is where I need some inputs on how to handle this.

{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,121 Reputation points Microsoft Employee
    2021-03-30T02:44:57.473+00:00

    Hi @Asif ,

    For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

    Per my poor understanding, you could have a try to get the ISO week number of every date and combine the records which have the same ISO week number.

    Please refer below a simple example and check whether it is a little helpful:

    drop table if exists Incrementalload  
      
    create table Incrementalload   
    (BacthID Varchar(20),  
    BatchDate date,  
    Qty int)  
      
    insert into Incrementalload values  
    ('A00001','2021-03-30',100),  
    ('A00001','2021-03-31',200),  
    ('A00002','2021-04-01',300),  
    ('A00002','2021-04-02',400),  
    ('A00003','2021-03-22',500),  
    ('A00003','2021-03-24',600)  
      
    ;with cte as (  
    select *,DATEPART(isowk, BatchDate) isowk  
    from Incrementalload)  
    select isowk,sum(qty) Total  
    from cte   
    group by isowk  
    

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    No comments

0 additional answers

Sort by: Most helpful