How to Archive Old Records from a History Table

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

Archiving Old Records from a History Table

Write a SQL query to archive old records from a history table.

Solution:

-- Archive records older than one year from the history table.
INSERT INTO ArchivedEmployeeHistory
SELECT * FROM EmployeeHistory
WHERE SysEndTime < DATEADD(YEAR, -1, GETUTCDATE());

DELETE FROM EmployeeHistory
WHERE SysEndTime < DATEADD(YEAR, -1, GETUTCDATE());

Explanation:

Purpose of the Query :

The goal is to demonstrate how to archive old records from a history table to manage storage.

Key Components :

INSERT INTO ArchivedEmployeeHistory: Moves old records to an archive table.
DELETE FROM EmployeeHistory: Removes archived records from the history table.
DATEADD(YEAR, -1, GETUTCDATE()): Calculates the cutoff date for archiving.

Why Archive Old Records? :

Archiving reduces storage overhead while preserving historical data.
It improves query performance on the history table.

Real-World Application :

In financial systems, archiving old transactions supports long-term retention policies.

Additional Notes:

Create an archive table with the same schema as the history table.
Schedule regular archiving to prevent excessive growth.
Important Considerations:

Ensure that archived data remains accessible for compliance.

For more Practice: Solve these Related Problems:

Write a SQL query to archive records older than two years from a temporal EmployeeHistory table into an archive table.
Write a SQL query to move outdated product records from a ProductHistory table to a long-term storage table.
Write a SQL query to clean up a temporal OrderHistory table by archiving records older than one year.
Write a SQL query to transfer historical customer data from a CustomerHistory table to an archive for compliance purposes.

Go to:

PREV : Enabling System-Versioning on an Existing Table.
NEXT : Restoring a Table to a Previous State Using Temporal Data.

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