INSERT Statement (ANSI Valid-Time Table Form) | Teradata Vantage - 17.00 - INSERT (ANSI Valid-Time Table Form) - Teradata Database

Teradata Vantage™ - ANSI Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1186-170K

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 the 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');
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 extension to ANSI that can be used when inserting rows into valid-time tables. It resolves to the maximum system DATE or TIMESTAMP value in accordance with the data type defined for the valid-time period ending bound column.