Make a "normal" table as temporal table

Question

I have a table created like this:

CREATE TABLE address_user 
(
    [username] VARCHAR(13) NOT NULL,
    [address] CHAR(58) NOT NULL,
    [id] BIGINT NOT NULL,

    CONSTRAINT [PK_ address_user] 
        PRIMARY KEY CLUSTERED ([id] ASC)
);

Now I want to be able to keep the history modification of this table, so I want to make it as temporal table. I know the script to create a temporal table, the final result should be:

CREATE TABLE address_user 
(
    [username] VARCHAR(13) NOT NULL,
    [address] CHAR(58) NOT NULL,
    [id] BIGINT NOT NULL,
    [sys_start_time] DATETIME2(7) 
        GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    [sys_end_time] DATETIME2 (7) 
        GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,

    PERIOD FOR SYSTEM_TIME ([sys_start_time], [sys_end_time]),
    CONSTRAINT [PK_ address_user] 
        PRIMARY KEY CLUSTERED ([id] ASC)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE=[dbo].[address_user_history], DATA_CONSISTENCY_CHECK=ON));

The easy way to do that is just delete the previous table, and recreate the table with the good schema.

However, I have a lot of information in my table, save the data and delete the table, recreate it and re-insert the data make me uncomfortable.

So if you have a solution to transform the first table in temporal table without the need to delete everything and recreate it, it should be a great help!

Answer 1

Create the new table address_user_new, insert the data, then use sp_rename to rename address_user to address_user_old and address_user_new to address_user. This can all be done in a transaction to ensure ensure that the transition is atomic and apparently-instantaneous. eg

if object_id('address_user') is not null
  ALTER TABLE address_user SET ( SYSTEM_VERSIONING = OFF)
go
if object_id('address_user_new') is not null
  ALTER TABLE address_user_new SET ( SYSTEM_VERSIONING = OFF)
go
drop table if exists address_user
drop table if exists address_user_history
drop table if exists address_user_new
drop table if exists address_user_old
go

CREATE TABLE address_user 
(
    [username] VARCHAR(13) NOT NULL,
    [address] CHAR(58) NOT NULL,
    [id] BIGINT NOT NULL,

    CONSTRAINT [PK_address_user] 
        PRIMARY KEY CLUSTERED ([id] ASC)
);

go
CREATE TABLE address_user_new 
(
    [username] VARCHAR(13) NOT NULL,
    [address] CHAR(58) NOT NULL,
    [id] BIGINT NOT NULL,
    [sys_start_time] DATETIME2(7) 
        GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    [sys_end_time] DATETIME2 (7) 
        GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,

    PERIOD FOR SYSTEM_TIME ([sys_start_time], [sys_end_time]),
    CONSTRAINT [PK_address_user_new] 
        PRIMARY KEY CLUSTERED ([id] ASC)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE=[dbo].[address_user_history], DATA_CONSISTENCY_CHECK=ON));

go


set xact_abort on
begin transaction

insert into address_user_new(username,address,id)
select username,address,id
from address_user with (tablockx)

exec sp_rename 'address_user', 'address_user_old', 'OBJECT'
exec sp_rename 'PK_address_user', 'PK_address_user_old', 'OBJECT'

exec sp_rename 'address_user_new', 'address_user', 'OBJECT'
exec sp_rename 'PK_address_user_new', 'PK_address_user', 'OBJECT'

commit transaction