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

To perform a current valid-time insert into a valid-time table, use the CURRENT VALIDTIME qualifier.

Consider the following valid-time table:

   CREATE MULTISET TABLE Policy(
      Policy_ID INTEGER,
      Customer_ID INTEGER,
      Policy_Type CHAR(2) NOT NULL,
      Policy_Details CHAR(40),
      Validity PERIOD(DATE) NOT NULL AS VALIDTIME
      )
   PRIMARY INDEX(Policy_ID);

The following statement performs a current valid-time insert into the Policy 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. The system timestamps the value of the valid-time column.

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

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

   CURRENT VALIDTIME INSERT INTO Policy
      (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)');