Temporal Tables – Editing Historical Data
Published: Jul 23, 2019
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)
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.

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

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron