Photo by Jan Antonin Kolar on Unsplash

How to modify data in a temporal/versioned table

Temporal tables, also known as system-versioned temporal tables, allow you to keep a full history of data changes in a separate table called a history table. This history table is linked to the main table and is automatically populated by the database system whenever a row in the main table is updated or deleted.

Given this behavior, direct modifications (like DELETE or UPDATE operations) to the history table are generally not allowed, to ensure data integrity and the accuracy of the history.

⚠️ If you’re trying to delete rows from the main table, and an error is being raised, make sure you’re not directly targeting the history table by mistake. If you delete rows from the main table, the corresponding rows in the history table will be preserved (that’s the whole point of temporal tables). If you’re trying to clean up or archive old history data, consider looking into best practices for managing data in temporal tables.

If you really need to modify the history table:

-- Set system-versioning to OFF
ALTER TABLE YourMainTableName SET (SYSTEM_VERSIONING = OFF);

-- Make your changes to the history table

-- Set system-versioning back to ON
ALTER TABLE YourMainTableName SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = YourHistoryTableName));