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.
2 Answers
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;
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.