Sql-server – SQL Server 2016 Temporal Tables

importsql serversql-server-2016temporal-tables

Has anyone tried prepopulating temporal tables with old history data? Before we decide to use temporal tables we need to be able to import our old history into them.

Best Answer

Yes, you can import old history into history tables. Here is a quick example that shows how to do this - you need to turn off system versioning temporarily.

CREATE TABLE dbo.TemporalExampleHistory
(
  TemporalID int NOT NULL,
  UserName   sysname,
  ValidFrom  datetime2 NOT NULL,
  ValidTo    datetime2 NOT NULL
);
GO

CREATE TABLE dbo.TemporalExample
(
  TemporalID int PRIMARY KEY,
  UserName   sysname,
  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
(
  SYSTEM_VERSIONING = ON 
  (
    HISTORY_TABLE = dbo.TemporalExampleHistory
  )
);

Now, insert some data:

INSERT dbo.TemporalExample(TemporalID, UserName)
  VALUES(1,N'Bob'),(2,N'Frank');

-- update a row to make some history:

UPDATE dbo.TemporalExample
  SET UserName = N'Frankie'
  WHERE TemporalID = 2;

Validate there is a system-versioned row in history table:

SELECT * FROM dbo.TemporalExampleHistory;

Now, to insert rows into history. Execute these steps one at a time; the parser will prevent the insert if these are all in a single batch.

BEGIN TRANSACTION; -- may want SERIALIZABLE here

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

INSERT dbo.TemporalExampleHistory(TemporalID, UserName, ValidFrom, ValidTo)
  SELECT TOP (1) TemporalID, N'Little Frankie', '19000101', ValidFrom
    FROM dbo.TemporalExampleHistory 
    WHERE TemporalID = 2
    ORDER BY ValidFrom;

ALTER TABLE dbo.TemporalExample SET
(
  SYSTEM_VERSIONING = ON 
  (
    HISTORY_TABLE = dbo.TemporalExampleHistory,
    DATA_CONSISTENCY_CHECK = ON
  )
);

COMMIT TRANSACTION;

Now validate there are now two versioned rows of id = 2 in history table:

SELECT * FROM dbo.TemporalExampleHistory;
Related Question