Convert Transaction-Time to System-Versioned Table | Vantage - 17.00 - Method 3: INSERT ... SELECT to New Table When Transaction-Time Column is a Period Data Type - Teradata Database

Teradata Vantage™ - ANSI Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1186-170K
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.