Backfill a temporal table

Question

Last year I was using my own hand rolled 'temporal' tables. So in Jan 2017 I did my raw data import and performed inserts, updates, deletes. Feb 2017 I did the same using the Feb raw data files. I did this for all month ends up to and including Dec 2017.

I have all the raw month end data files that I used for the imports and I'd like to now scrap that rubbish hand rolled 'temporal' tables solution and use the nice Microsoft supplied temporal tables. My problem is I would need to set the computer system date back to Jan 2017, do my Jan 2017 ETL, change the system date to Feb 2017, do my Feb 2017 ETL, change the system date to Mar 2017, do the Mar 2017 ETL, etc, etc.

Any alternative to changing the system date ?

Answer 1

You don't need to change the server time to have the correct time.

The process is described below.

1. Create all history tables first for each table (same structure as if you create temporal table with default history table).
2. Create indexes for all history table (same structure as if you create temporal table with default history table).
3. Populate the history tables directly, including SysStartTime, SysEndTime columns with desired datetime values
4. Alter tables to enable versioning, specifying history table.

https://learn.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-table

(see Alter Non-Temporal Table to be System-Versioned Temporal Table paragraph)

Alternatively, you can:

1. Drop and recreate all tables with enabled versioning (specifying your own (non-default) history tables).
2. Run ETLs
3. Stop versioning temporary
4. Update history tables to have correct values for SysStartTime/SysEndTime
5. Re-enable versioning (specifying existing history tables)