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.