convert ordinary sqlserver table, to temporal table

table_to_temporal_table_sqlserver.sql

declare @table nvarchar(255)
declare @sql nvarchar(max)

-- setting table name 

set @table = 'dbo.test'

-- adding starttime , endtime columns

set @sql = '
ALTER TABLE '+@table+'
ADD StartTime DATETIME2;'
print(@sql) 
 
set @sql = '
ALTER TABLE '+@table+'
ADD EndTime DATETIME2;'
print(@sql) 

-- set default value for starttime and endtime

set @sql = 'UPDATE '+@table+' SET StartTime = ''19000101 00:00:00.0000000'', EndTime = ''99991231 23:59:59.9999999'';'
print(@sql) 

set @sql = 'ALTER TABLE '+@table+'
ALTER COLUMN StartTime DATETIME2 NOT NULL;'
print(@sql) 

-- starttime and endtime cannot be null

set @sql = 'ALTER TABLE '+@table+'
ALTER COLUMN EndTime DATETIME2 NOT NULL;'
print(@sql) 


-- setting period

set @sql = 'ALTER TABLE '+@table+'
ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime);'
print(@sql)


-- finish him!

set @sql = 'ALTER TABLE '+@table+'
SET(SYSTEM_VERSIONING = ON (HISTORY_TABLE = '+@table+'_History, DATA_CONSISTENCY_CHECK = ON));'
print(@sql)