Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 215
Hamed Roshangar Avatar asked Nov 09 '16 12:11

Hamed Roshangar


1 Answers

  1. Create your temporal table.

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

  3. Drop your original table.

like image 142
sagi Avatar answered Sep 18 '22 17:09

sagi