How to Archive Old Records from a History Table

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.

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

Previous SQL Exercise: Enabling System-Versioning on an Existing Table.
Next SQL Exercise: Restoring a Table to a Previous State Using Temporal Data.