SQL Server 2016 Temporal Tables

Question

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.

Answer 1

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;

Answer 2

Great question!

To copy the data from your current log tables, you could write a simple script to populate the history table. Remember, you'll have temporal and history tables created, so be sure to insert in the correct one. Assuming that your current log table keeps track of "insert", "update", and "delete", you'll need to write a script that could insert like so:

• INSERT – Insert all "insert" commands from current log table. StartTime set to current table's [logDT], EndTime is set to max value (9999-12-31)

• UPDATE – Since Update statement inserts the old row in history table, your script will have to find all the rows that your Update statement is actually updating (these will be "insert" or "updatE" commands) and enter those rows in. StartTime will be [logDT], EndTime is set to max value (9999-12-31)

• DELETE - Same as Update, you'll have to find the old row that this statement actually deleted. EndTime will be [logDT] of Delete command and StartTime would be [logDT] of the previous Update or Insert that this statement Deleted

I am not writing a script because it changes for all use cases but hope this gives you an idea of what you could do to import old log tables into history table.