SQL Server 2016 - Period start and end datetime set manually

Question

I'm trying to load some historical data that is time based and I would like it to be in a temporal table in SQL Server 2016.

As far as I see, the period start and end date cannot be manually set, they are set at insert/update/delete time using the system time.

However, the files I am trying to load have a specific date on when the data would have been available and I would like that to be reflected in the period start date so I can query it accordingly.

Is there any way to set the period start and end time manually?

Answer 1

You can do this by disabling system versioning, inputting the data and enabling system versioning again. You have to be careful not to insert overlapping dates as that will make your history table unusable. I have found this to be very useful when migrating data from one system to another.

Inserting data into your main table

First turn off your system versioning and then drop it completely.

ALTER TABLE dbo.ExampleTable SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE dbo.ExampleTable DROP PERIOD FOR SYSTEM_TIME;

-- Insert your data with your start date and the default end date.

-- Add your period for system_time and enable system versioning

ALTER TABLE dbo.ExampleTable ADD PERIOD FOR SYSTEM_TIME(StartDate, EndDate);
ALTER TABLE dbo.ExampleTable SET (SYSTEM_VERSIONING = ON(History_Table = dbo.ExampleTableHistory));

Please note if the columns are currently hidden this will remove that.

Inserting data into your history table.

For the History data you are able to insert directly into the history table by turning the system versioning off and inserting the data.

ALTER TABLE dbo.ExampleTable SET (SYSTEM_VERSIONING = OFF);

-- Insert your data into the dbo.ExampleTableHistory table, with start dates and end dates.

-- Enable system versioning

ALTER TABLE dbo.ExampleTable SET (SYSTEM_VERSIONING = ON (History_Table = dbo.ExampleTableHistory));