CODEWITHSUNDEEP
t-sqlsql-server-2016temporaltemporal-database
Skaterhaz
Skaterhaz

Reputation: 299

SQL Server 2016 - System_Versioned table - Re-create existing table with meaningful SysStartTime and SysEndTime fields

We have two tables, Events and EventsLog. The Events table is shown below

enter image description here

The EventsLog table is used to keep a history of changes made to the Events table via a trigger on UPDATE in the Events table.

Since Temporal tables was added to SQL 2016, we now want to use system_versioning to keep a log of our changes to the Events table.

We have setup a new EventsTemporal table to migrate over the existing data and the EventsTemporal_History table is working nicely, logging historical entries from EventsTemporal

However, we cannot override the SysStartTime and SysEndTime. Currently we can only apply default into these fields when performing an INSERT statement.

The question is, using any method possible, can we override the SysStartTime and SysEndTime fields so we can INSERT meaningful datetime2's into them, to make use of the EventsTemporal_History table?

This is currently how the EventsTemporal table looks. Notice the SysStartTime field, we want to be able to populate these fields with our own 'timestamp' and not use SQLs default.

enter image description here

Ideally we want to be able to populate these fields like the below.

enter image description here

Upvotes: 0

Views: 260

Answers (1)

qxg
qxg

Reputation: 7036

  1. Disable SYSTEM_VERSIONING on EventsTemporal.

    ALTER TABLE EventsTemporal SET ( SYSTEM_VERSIONING = OFF );

  2. Update EventsTmeporal as normal table.

    UPDATE D
SET D.SysStartDate = S.EventDate
FROM dbo.EventsTemporal AS D
INNER JOIN dbo.Events AS S 
  ON D.EventId = S.EventId;

  3. Enable SYSTEM_VERSIONING on EventTemporal;

    ALTER TABLE dbo.EventsTemporal ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);

ALTER TABLE dbo.EventsTemporal ALTER COLUMN SysStartTime ADD HIDDEN;
ALTER TABLE dbo.EventsTemporal ALTER COLUMN SysEndTime ADD HIDDEN;

ALTER TABLE dbo.EventsTemporal
    SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.EventsTemporal_History ) );

Upvotes: 1

Related Questions