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

Teradata Database ANSI Temporal Table Support

prodname
Teradata Database
vrm_release
15.00
15.10
category
Programming Reference
featnum
B035-1186-015K

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.