Method 3: INSERT ... SELECT to New Table When Transaction-Time Column is a Period Data Type - Teradata Database

This method:

does not require the NONTEMPORAL privilege.

can be used on transaction-time tables that are row-partitioned on the beginning or ending bound of the transaction-time period.

1 Create a new table with columns that match the non-transaction-time columns of the existing table. Add two new TIMESTAMP(6) WITH TIME ZONE columns that will hold the beginning and ending bound values for the ANSI system-time derived period column. For the purposes of this procedure, assume the columns are named sys_start and sys_end.

2 Use a NONSEQUENCED INSERT ... SELECT to copy the rows of the transaction-time table into the new table.

3 Use ALTER TABLE to create the SYSTEM_TIME derived period column and to add attributes to the set the sys_start and sys_end columns in the same ALTER TABLE statement:

ALTER TABLE new_table_name
 ADD PERIOD FOR SYSTEM_TIME(sys_start,sys_end) 
 ADD sys_start TIMESTAMP(6) WITH TIME ZONE NOT NULL 
   GENERATED ALWAYS AS ROW START 
 add sys_end TIMESTAMP(6) WITH TIME ZONE NOT NULL 
   GENERATED ALWAYS AS ROW END;

4 Note the constraints on the transaction-time table.

5 Drop the transaction-time table.

6 Rename the new table as the old table.

7 Add system versioning to make the new table an ANSI system-time temporal table:

ALTER TABLE new_table_name 
 ADD SYSTEM VERSIONING;

8 Recreate all the constraints that were dropped in step 2. Note that ANSI constraints behave as NONSEQUENCED constraints.