How can alter existence tables to SQL Temporal table by keeping data?

Question

I have many table with data, which I want to convert to Microsoft Temporal table, But when I want to convert temporal table cause lost my data. My code is:

Alter TABLE dbo.Employee   
(    
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED   
  , [Name] nvarchar(100) NOT NULL  
  , [Position] varchar(100) NOT NULL   
  , [Department] varchar(100) NOT NULL  
  , [Address] nvarchar(1024) NOT NULL  
  , [AnnualSalary] decimal (10,2) NOT NULL  
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START  
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END  
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)  
 )    
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory)); 

how can alter existence tables to Sql temporal table by keeping data?

Salot

Answer 1

First you should add two columns for system time period to any tables. like this:

CREATE TABLE DepartmentHistory   
(    
     DeptID int NOT NULL  
   , DeptName varchar(50) NOT NULL  
   , SysStartTime datetime2 NOT NULL  
   , SysEndTime datetime2 NOT NULL   
);   
GO   
CREATE CLUSTERED COLUMNSTORE INDEX IX_DepartmentHistory   
   ON DepartmentHistory;   
CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_PERIOD_COLUMNS   
   ON DepartmentHistory (SysEndTime, SysStartTime, DeptID);   
GO   
CREATE TABLE Department   
(    
    DeptID int NOT NULL PRIMARY KEY CLUSTERED  
   , DeptName varchar(50) NOT NULL  
   , SysStartTime datetime2  NOT NULL  
   , SysEndTime datetime2 NOT NULL           
) ;

In the up codes, SysStartTime and SysEndTime has system period time columns.

Then you can convert them to Temporal tables easily:

ALTER TABLE dbo.Department   
   ADD PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])   
ALTER TABLE dbo.Department      
   SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory, DATA_CONSISTENCY_CHECK = ON));  

And, if you have temporal table and you want to edit tables schema, so you can do it by this code:

ALTER TABLE Test.dbo.Department   
   SET (SYSTEM_VERSIONING = OFF) 

Leban

Answer 2

Turn ON system versioning in Employee table in two steps

1. add new period columns (HIDDEN)

2. create default history table

   ALTER TABLE Employee   
   ADD   
   ValidFrom datetime2 (2) GENERATED ALWAYS AS ROW START HIDDEN    
       constraint DF_ValidFrom DEFAULT DATEADD(second, -1, SYSUTCDATETIME())  
   , ValidTo datetime2 (2)  GENERATED ALWAYS AS ROW END HIDDEN     
       constraint DF_ValidTo DEFAULT '9999.12.31 23:59:59.99'  
   , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);   
   
   ALTER TABLE Employee    
   SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Employee_History));  
   

After executing the above script, all data changes will be collected transparently in the history table. In typical data audit scenario, you would query for all data changes that were applied to an individual row within a period of time of interest. The default history table is created with clustered row-store B-Tree to efficiently address this use case.

Elasticity

Answer 3

1. Create your temporal table.

2. Insert the data from your original table to the temporal table.

3. Drop your original table.

Psychopathy

I have many tables maybe amount 100 tables and I think this way is not usable for all tables every time. thanks for your answer. – Salot