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

To insert data into a bitemporal table that is open in the transaction-time dimension and current in the valid-time dimension, use the CURRENT VALIDTIME qualifier.

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 statement performs a current valid-time insert into the Policy_History table. Because the INSERT uses a positional assignment list (where no column names are provided), no value for the valid-time column can be specified. Because the system inserts the value for the transaction-time column, no value for the transaction-time column can be specified.

   CURRENT VALIDTIME INSERT INTO Policy_History
      VALUES (541077, 766492008, 'AU', 'STD-CH-344-YXY-00');

The following statement also performs a current valid-time insert into the Policy_History table. Because the INSERT uses a named list, a value for the valid-time column can be specified. Because the system inserts the value for the transaction-time column, no value for the transaction-time column can be specified.

   CURRENT VALIDTIME INSERT INTO Policy_History
      (Policy_ID, Customer_ID, Policy_Type, Policy_Details, Validity)
      VALUES (541145, 616035020, 'AU', 'STD-CH-348-YXN-01',
         PERIOD '(2009-12-03, 2010-12-01)');