I am using sql server 2012 ent environment and am trying to implement partitioning for a single table which normally gets processed once a month. I would like to change it to process daily for reporting purposes.
There are three identical tables dynamically partitioned by transaction date: Partition 1 – <=last year Partition 2 – Current year to end of last month Partition 3 – Current month to date
I process new data into table B, then use alter table switch to move the current data partition over to the delivery table A ( table C is kept empty). Sometimes I will get data during processing that is actually an update to data in an older partition. This data falls into the older partition on the processing table so is not moved over to the delivery table.
Other than reprocessing the entire data set, what is the best way to move/process older-partition data from the processing table into the delivery table? TYIA!