Temporal Tables – Editing Historical Data
Published: Dec 05, 2021
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:

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)

-- update the record
update HistoryDimCompany
set companyName = 'Pear Computer Corp'
where companyID = 1
and validFrom = '2019-03-07 07:11:44.9103845'

-- add versioning, with a specified History table
alter table DimCompany set
system_versioning = on
history_table = dbo.HistoryDimCompany

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.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Kutech  CEO
Kevin  Urquhart


I am a SQL Server DBA, Architect, Developer, Trainer, and the owner and CEO of Kutech. This blog has been going for nearly 10 years now over several guises and this is its new home. I hope you find it useful whatever your endeavour.


© Copyright 2021 Kutech Solutions Ltd.

Our site uses cookies only to personalise content and analyse traffic. We do not use cookies for advertising. Policy