One of the fundamental concepts which you need to understand when dealing with temporal tables is that they system managed, and intended to provide an indelible record how data appeared at any period in time.
There are times however when you may have a need to prepopulate a temporal table, either when migrating history to a new temporal table, or when setting up test scenarios. To do this you need to overcome a couple of restrictions:
- The history table is read-only; all inserts, deletes and updates will be blocked.
- The current data table has two read-only fields; specifically, the Valid From and Valid To fields, which indelibly define the time range for which a specific version of a record was valid.
Preparing the Demo
if not exists (Select * from Information_Schema.Schemata where Schema_Name = 'History')
begin
exec ('Create Schema History')
;
end
go
Create table History.Applicant
(
ApplicantID int not null,
Forename varchar(50),
Surname varchar(50),
DateOfBirth date,
ValidFrom datetime2 NOT NULL,
ValidTo datetime2 NOT NULL
) with (Data_Compression = Page)
go
if not exists (Select * from Information_Schema.Schemata where Schema_Name = 'CRM')
begin
exec ('Create Schema CRM')
;
end
go
Create table CRM.Applicant
(
ApplicantID int identity(1,1) primary key,
Forename varchar(50),
Surname varchar(50),
DateOfBirth date,
ValidFrom datetime2 GENERATED ALWAYS AS ROW START NOT NULL ,
ValidTo datetime2 GENERATED ALWAYS AS ROW END NOT NULL ,
PERIOD FOR SYSTEM_TIME (ValidFrom,ValidTo)
) with (Data_Compression = Page, SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.Applicant))
Go
The scenario
- Jon Doe – There should only be one version of this applicant, and it should have been valid from the start of the year.
- Jane Dough – There should be 2 versions of this applicant; the first valid from the start of the year, and the second valid from the start of June.
Set identity_Insert CRM.Applicant ON
go
insert CRM.Applicant (ApplicantID, Forename, Surname, DateOfBirth, ValidFrom, ValidTo)
values (1, 'Jon','Doe','1972-08-12', '2020-01-01 00:00:00', '9999-12-31 23:59:59.9999999'),
(2, 'Jane','Dough','1977-05-23', '2020-06-01 00:00:00', '9999-12-31 23:59:59.9999999')
go
Set identity_Insert CRM.Applicant OFF
go
insert History.Applicant (ApplicantID, Forename, Surname, DateOfBirth, ValidFrom, ValidTo)
values (2, 'Jane','Doe','1976-05-23', '2020-01-01 00:00:00', '2020-05-31 23:59:59.9999999')
go
Msg 13536, Level 16, State 1, Line 45
Cannot insert an explicit value into a GENERATED ALWAYS column in table 'Sandbox.CRM.Applicant'. Use INSERT with a column list to exclude the GENERATED ALWAYS column, or insert a DEFAULT into GENERATED ALWAYS column.
Msg 13559, Level 16, State 1, Line 55
Cannot insert rows in a temporal history table 'Sandbox.History.Applicant'.
Overcoming both of these issues require that you disable the system versioning on your table, essentially decoupling your current and history tables. This can be accomplished with the following script:
ALTER TABLE CRM.Applicant SET ( SYSTEM_VERSIONING = OFF )
GO
ALTER TABLE CRM.Applicant Drop Period FOR SYSTEM_TIME
GO
Set identity_Insert CRM.Applicant ON
go
insert CRM.Applicant (ApplicantID, Forename, Surname, DateOfBirth, ValidFrom, ValidTo)
values (1, 'Jon','Doe','1972-08-12', '2020-01-01 00:00:00', '9999-12-31 23:59:59 .9999999'),
(2, 'Jane','Dough','1977-05-23', '2020-06-01 00:00:00', '9999-12-31 23:59:59 .9999999')
go
Set identity_Insert CRM.Applicant OFF
go
insert History.Applicant (ApplicantID, Forename, Surname, DateOfBirth, ValidFrom, ValidTo)
values (2, 'Jane','Doe','1976-05-23', '2020-01-01 00:00:00', '2020-05-31 23:59:59 .9999999')
go
Select * from CRM.Applicant
Select * from History.Applicant
Now that we have our data as we want it, we need to reinstate the system versioning. This too is a two-step process. Firstly, we need to recreate the period on CRM.Applicant:
ALTER TABLE CRM.Applicant Add PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
Go
ALTER TABLE CRM.Applicant SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = History.Applicant ) )
GO
It must be noted that dates within the history table are validated at this stage, and any gaps between the maximum ValidTo values in History.Applicant and the ValidFrom value of their respective record in CRM.Applicant will cause this to fail.
An accompanying notebook s is available here.
Happy SQLing!