Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Temporal Tables – Editing Historical Data
Published: Dec 02, 2020
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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.