Convert Transaction-Time to System-Versioned Table with ALTER TABLE | Vantage - 17.10 - Method 1: Alter Existing Transaction-Time Table - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - ANSI Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1186-171K
Language
English (United States)
This method:
  • requires that the executor have the NONTEMPORAL privilege in the database, and that the database be enabled to recognize that privilege. Read more about the NONTEMPORAL privilege in Teradata Vantageā„¢ - Temporal Table Support, B035-1182.
  • cannot be used if the transaction-time table is row partitioned on the beginning or ending bound of the transaction-time.
  • is not recommended for large, column-partitioned tables because for these tables the update operation in Step 4 can be very resource-intensive and time-consuming.
  1. Note all the constraints on the transaction-time table.
  2. Drop all the constraints from the transaction-time table.
  3. Use NONTEMPORAL ALTER TABLE to add two new columns of type TIMESTAMP(6) WITH TIME ZONE. For the purposes of this procedure, assume the columns are named sys_start and sys_end. These will hold the beginning and ending bound values of the new SYSTEM_TIME derived period column.
  4. Use NONTEMPORAL UPDATE to populate the new columns with the start and end values of the existing transaction-time columns or derived period column.
  5. Use NONTEMPORAL ALTER TABLE to drop the existing transaction-time column. Use the WITHOUT DELETE option to preserve the historical closed rows, which would otherwise be deleted automatically when you drop the transaction-time column:
    ALTER TABLE  transaction_time_table_name  
      DROP  transaction_time_column  WITHOUT DELETE
  6. 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  transaction_time_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;
  7. Add system versioning to make the new table an ANSI system-time temporal table:
    ALTER TABLE  transaction_time_table_name  
     ADD SYSTEM VERSIONING;
  8. Recreate all the constraints that were dropped in step 2. Note that ANSI constraints behave as NONSEQUENCED constraints.