17.10 - INSERT/INSERT SELECT Syntax Elements (Temporal Forms) - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1182-171K
Language
English (United States)
values_spec
{ expression [,...] | column_name = expression [,...] }
CURRENT VALIDTIME
Specifies that the insert is current in valid time.
At least one of the referenced tables or the target table must be a table with a valid-time column. The CURRENT VALIDTIME qualifier is used to qualify rows from the referenced tables in the valid-time dimension. In the transaction-time dimension, open rows qualify.
VALIDTIME and SEQUENCED VALIDTIME
Specifies that the insert is sequenced in valid time.
At least one of the referenced tables or the target table must be a table with a valid-time column. The VALIDTIME or SEQUENCED VALIDTIME qualifier is used to qualify rows from the referenced tables in the valid-time dimension. In the transaction-time dimension, open rows qualify.
Use a sequenced valid-time insert to insert history, current, or future rows.
period_expression

Specifies the period of applicability for the DML statement.

The period of applicability must be a period constant expression that does not reference any columns, but can reference parameterized values and the TEMPORAL_DATE or TEMPORAL_TIMESTAMP built-in functions.

The period of applicability can also be a self-contained noncorrelated scalar subquery that is always nonsequenced in the time dimensions regardless of the temporal qualifier for the DML statement.

If a period_expression is specified, the valid-time column cannot be specified or referenced anywhere in the query. If the valid-time column is a derived period column, the component columns cannot be specified or referenced anywhere in the query.

If period_expression is omitted, the period of applicability defaults to PERIOD'(0001-01-01, UNTIL_CHANGED)' for a PERIOD(DATE) valid-time column or PERIOD '(0001-01-01 00:00:00.000000+00:00, UNTIL_CHANGED)' for a PERIOD(TIMESTAMP( n ) WITH TIME ZONE) valid-time column, where precision n and WITH TIME ZONE are optional.

The period of applicability can be specified only for an INSERT SELECT statement, where the statement specifies a subquery.
NONSEQUENCED VALIDTIME
Specifies that the insert is nonsequenced in valid time.
At least one of the referenced tables or the target table must be a table with a valid-time column.
Use a nonsequenced valid-time insert to insert history, current, or future rows.
NONTEMPORAL
Specifies that the insert is a nontemporal insert on a table with transaction time.
table_name
Specifies the name of a temporal table.
column_name [ … , column_name]
Specifies a named list of columns in the target table.
expression [ … , expression]
Specifies a positional assignment list of values to insert into the target table.
column_name = expression [ … , column_name = expression]
Specifies an assignment list of column names in the target table and the values to assign them.
DEFAULT VALUES
Specifies that a row consisting of default values is to be added to table_name.