Example: Current Valid-Time Insert into a Valid-Time Table - Analytics Database - Teradata Vantage

Temporal Table Support

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
eud1628112402879.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
fif1472244754791
lifecycle
latest
Product Category
Teradata Vantage™

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)');