15.10 - INSERT (ANSI Valid-Time Table Form) - Teradata Database

Teradata Database ANSI Temporal Table Support

prodname
Teradata Database
vrm_release
15.00
15.10
category
Programming Reference
featnum
B035-1186-015K

Purpose  

Add new rows to temporal tables.

Syntax  

There is no special temporal syntax for inserting rows into temporal tables. Use the standard SQL INSERT statement. However, note the following:

  • You must include values for the beginning and ending bound columns that constitute the valid-time derived period column.
  • As for any type of derived period column in Teradata Database, you cannot insert Period type values for the derived period column itself.
  • Example : Inserting Rows into a Valid-Time Table

    INSERT INTO employee_valid_time VALUES 
     (1001,'Sania',’TW08’,DATE'2002-01-01',DATE'2006-12-31');
    INSERT INTO employee_vt VALUES 
     (1004,'Fred',’PW12’, DATE'2001-05-01',UNTIL_CHANGED);
    INSERT INTO employee_vt VALUES 
     (1002,'Ash',’TA05’,DATE'2003-01-01',DATE'2003-12-31');
    INSERT INTO employee_vt VALUES 
     (1003,'SRK',’TM02’,DATE'2004-02-10',DATE'2005-02-10');
    INSERT INTO employee_vt VALUES 
     (1005,'Alice',’TW10’,DATE'2004-12-01',DATE’2005-12-01’);
    INSERT INTO employee_vt VALUES
     (1005,'Alice',’PW11’,DATE'2005-12-01',UNTIL_CHANGED);
    INSERT INTO cmployee_vt VALUES
     (1010,’Mike’,’TW07’,DATE'2015-01-01',DATE'2016-12-31');

    Note: Values for the start and end columns that constitute the valid-time period must be provided in the INSERT statement. UNTIL_CHANGED is a Teradata Database extension to ANSI that can be used when inserting rows into valid-time tables. It resolves to the maximum system DATE or TIIMESTAMP value in accordance with the data type defined for the valid-time period ending bound column.