Example: Nonsequenced Valid-Time Insert into a Bitemporal Table - Advanced SQL Engine - Teradata Database

A nonsequenced valid-time insert is similar to a conventional insert where the valid-time column is treated as any other column in the table. Because the system automatically inserts the value for the transaction-time column, the INSERT statement cannot specify a value for the transaction-time column.

Consider the following bitemporal table:

   CREATE MULTISET TABLE Policy_History(
      Policy_ID INTEGER,
      Customer_ID INTEGER,
      Policy_Type CHAR(2) NOT NULL,
      Policy_Details CHAR(40),
      Validity PERIOD(DATE) NOT NULL AS VALIDTIME,
      Policy_Duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL
         AS TRANSACTIONTIME)
   PRIMARY INDEX(Policy_ID);

The following statements perform nonsequenced valid-time inserts that are open in the transaction-time dimension into the Policy_History table.

   NONSEQUENCED VALIDTIME INSERT INTO Policy_History
      VALUES (540232, 450909234, 'AU', 'STD-CH-344-YYY-00',
         PERIOD (DATE '2009-11-01', UNTIL_CHANGED));

   NONSEQUENCED VALIDTIME INSERT INTO Policy_History
      (Policy_ID, Customer_ID, Policy_Type, Policy_Details, Validity)
      VALUES (540944, 120344567, 'AU', 'STD-PL-332-YXY-01',
         PERIOD (DATE '2010-02-03', DATE '2011-02-02'));