17.05 - Current Inserts - Teradata Database

Teradata Vantage™ - Temporal Table Support

Advanced SQL Engine
Teradata Database
June 2020
Programming Reference

When an INSERT or INSERT ... SELECT statement is issued on a temporal table, and the statement either specifies the CURRENT VALIDTIME qualifier or does not specify any temporal qualifier, the insert is a current insert.

You should always explicitly specify the value of the valid-time column when performing a current insert on a table that has a valid-time column. The valid-time period must overlap TEMPORAL_TIMESTEMP or TEMPORAL_DATE, depending on the type of the valid-time column.

The following information applies to current inserts:

  • Do not use a positional assignment list to specify column values for a current insert.

    If an INSERT statement uses a positional assignment list, the list can not include a value for the valid-time column. The valid-time column position is skipped when mapping values to the columns, and the column is always set to the system-defined default value.

    The system-defined default values for a valid-time column are (TEMPORAL_TIMESTAMP to UNTIL_CHANGED) and (TEMPORAL_DATE to UNTIL_CHANGED), depending on the period data type of the column.

    If an INSERT … SELECT statement uses a positional assignment list, the values of the valid-time and transaction-time columns from the source table are not copied into the target table, but are replaced by the system-defined default value. This is true even if the statement specifies SELECT * in the SELECT statement or projects the valid-time and transaction-time columns explicitly.

  • If an INSERT statement uses a named list or assignment list, the list can specify a value for the valid-time column. If the named list or assignment list does not specify the valid-time column, the valid-time value is set to the system-defined default value.

    IF an INSERT ... SELECT statement uses a named list, a valid-time column value or any Period column value can be copied into the target table if the named list specifies a valid-time column. If the SELECT * asterisk notation is used in combination with explicitly specification of a temporal column, use the table_name.* notation. For more information see Asterisks in Select Lists.

    If a CURRENT VALIDTIME INSERT specifies a value for the valid-time column, the period must overlap the current time. To insert valid-time history or future rows into a table, use the SEQUENCED VALIDTIME qualifier to the INSERT statement.
  • Values can never be specified for a transaction-time column, unless the NONTEMPORAL qualifier is used. This column is automatically maintained by the system. For current inserts, the system defined default value for the transaction-time column is (TT_TIMESTAMP to UNTIL_CLOSED), where TT_TIMESTAMP is the timestamp value read from the system clock by each AMP during timestamping. The transaction-time column position is always skipped when mapping the inserted values to columns.
  • The SELECT statement of an INSERT … SELECT is executed as a current SELECT. The result rows of a current SELECT are inserted into the target table with period of validity set to (TEMPORAL_DATE to UNTIL_CHANGED) or (TEMPORAL_TIMESTAMP to UNTIL_CHANGED).