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.