I need to copy the values from a staging table into a production table in SQL Azure V12, considering the following rule:
I need to replace all rows for a given day (see datefield column) in the production table with all rows for the same given day in the staging table provided that the extraction date/time (see extract_datetime column) of the rows in the staging table are newer than what is in the production table.
EDIT: I also must include all new rows for days that don't already exist in the production table. See the purple rows in the table examples below.
The reason for this rule is that records tend to be amended and deleted from the system which we are extracting data from, so even though at one time a given day could have 200 entries for it, it might be that subsequently we only end up with 3 rows for that same given day.
If a day is included in a data extract, it will always have the most up-to-date records for that entire day (from 00:00:00 to 23:59:59 hrs).
In this example, the production table has 9 rows in total, as follows:
- 01/07/2016: 4 rows
- 02/07/2016: 2 rows
- 03/07/2016: 3 rows
Then we do a load what is in the staging table into the production table, and the following happens:
- 01/07/2016: The 4 rows in production for this day are replaced with the 1 row in the staging for this day, because what is in staging has a newer extraction date/time.
- 02/07/2016: The 2 rows in production for this day are replaced with the 4 rows in the staging for this day, because what is in staging has a newer extraction date/time.
- 03/07/2016: The 3 rows in production for this day are kept and those in the staging table are discarded, because what is in staging has an older extraction date/time.
- 07/07/2016: The 1 row in staging for this day is inserted into the production table. This is a brand new row and there is no match for it in the production table.
So in the end we end up with the following production table:
These data loads are never bigger than a few megabytes, so at other tables with less complex rules what I tend to do is:
- First delete all rows in staging for when there are newer records in production;
- Delete all rows in production for when there are newer rows in staging; then
- Insert the remaining rows from staging into production, then truncate staging for the next load.
In this case I am quick lost as on how to do this matching because it is not really in a row by row basis (so I think), so any advice on how to get this code working is greatly appreciated.