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

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".

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

Previous SQL Exercise: Querying the Latest Version of Each Row.
Next SQL Exercise: Querying Changes Made by a Specific User.