question

Neil-9764 avatar image
0 Votes"
Neil-9764 asked MelissaMa-msft commented

Incremental delta load

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.





sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Not clear to what you want to do?

... I come across a scenario where the months last weeks incremental data falls into two batchid's(month ending and new month starting), ...


Provide some example with the explanation.


1 Vote 1 ·

Hi @Neil-9764,

Could you please provide any update or more details?

Thank you!

Best regards
Melissa

1 Vote 1 ·

1 Answer

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @Neil-9764,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.