Temporal Tables – Editing Historical Data
Published: Jul 12, 2026
We all know that Temporal Tables don’t allow us to edit data in the Historical table. This is for all manner of incredibly sensible reasons (auditing etc) and therefore shouldn’t be breached.
However, there are also times when we very much need to correct some data historically and need it reflected accurately in the Historical table of our Temporal setup. Luckily this can be done.
Basically, we need to disconnect the Temporal Tables, modify the historical record, and then return the tables to their “Temporal” state.
We can do this as follows:
Actually, there’s a mistake… for the 6 minute period in the History table the company was actually known as “Pear Computer Corp” and we got it wrong. Therefore we now need to change this to reflect properly in any reports.
And there you go… we’ve edited the Historical data in a Temporal Table.
However, there are also times when we very much need to correct some data historically and need it reflected accurately in the Historical table of our Temporal setup. Luckily this can be done.
Basically, we need to disconnect the Temporal Tables, modify the historical record, and then return the tables to their “Temporal” state.
We can do this as follows:
select * from DimCompany
select * from HistoryDimCompany
Actually, there’s a mistake… for the 6 minute period in the History table the company was actually known as “Pear Computer Corp” and we got it wrong. Therefore we now need to change this to reflect properly in any reports.
-- Remove versioning
alter table DimCompany set (system_versioning = off)
go
-- update the record
update HistoryDimCompany
set companyName = 'Pear Computer Corp'
where companyID = 1
and validFrom = '2019-03-07 07:11:44.9103845'
go
-- add versioning, with a specified History table
alter table DimCompany set
(
system_versioning = on
(
history_table = dbo.HistoryDimCompany
)
)
go
select * from DimCompany
select * from HistoryDimCompany
select *
from DimCompany
for system_time all
And there you go… we’ve edited the Historical data in a Temporal Table.