How to Migrate Data from a Non-Temporal Table to a Temporal Table

Last update on September 25 2025 07:23:58 (UTC/GMT +8 hours)

Migrating Data from a Non-Temporal Table to a Temporal Table

Write a SQL query to migrate data from a non-temporal table to a temporal table.

Solution:

-- Create a temporal table.
CREATE TABLE TemporalEmployees (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Position NVARCHAR(50),
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TemporalEmployeeHistory));

-- Migrate data from the non-temporal table.
INSERT INTO TemporalEmployees (EmployeeID, Name, Position, SysStartTime, SysEndTime)
SELECT EmployeeID, Name, Position, GETUTCDATE(), '9999-12-31 23:59:59.9999999'
FROM NonTemporalEmployees;

Explanation:

Purpose of the Query :

The goal is to demonstrate how to migrate data from a non-temporal table to a temporal table.

Key Components :

CREATE TABLE: Creates a new temporal table.
INSERT INTO: Copies data from the non-temporal table to the temporal table.
Sets SysStartTime and SysEndTime for versioning.

Why Migrate Data? :

Migrating data enables historical tracking without redesigning the schema.
It ensures compliance with auditing requirements.

Real-World Application :

In legacy systems, migrating data adds auditing capabilities.

Additional Notes:

Ensure that the non-temporal table has a primary key.
Test migration procedures in a development environment first.
Important Considerations:

Validate data integrity after migration.

For more Practice: Solve these Related Problems:

Write a SQL query to migrate data from a non-temporal table named "LegacyEmployees" to a temporal table named "Employees".
Write a SQL query to copy historical data from a regular table named "OldSales" into a temporal table named "Sales".
Write a SQL query to transfer records from a non-temporal table named "ArchiveOrders" to a temporal table named "Orders".
Write a SQL query to move data from a legacy table named "PastProjects" to a temporal table named "Projects".

Go to:

PREV : Querying the Latest Version of Each Row.
NEXT : Querying Changes Made by a Specific User.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.