17.10 - Sequenced Inserts - 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)

A sequenced insert is similar to a current insert, but allows the user to specify the valid-time of the inserted rows. Inserted rows can be history, current, or future rows.

The INSERT statement cannot specify explicit values for the transaction-time column if the table has transaction time. The system maintains the transaction time. The transaction-time column name cannot be specified in the named list or assignment list of a sequenced INSERT statement. The system skips the transaction-time column position when mapping the values to the columns.

The period value specified for the valid-time column must be assignable to the valid-time column of the target table, and cannot be NULL.

For a target table with valid time where the INSERT statement does not specify a SELECT:

  • If a positional assignment list is specified, the valid-time value is read from the value list in the same position as that of the valid-time column position. If a value is specified, the value is assigned to the valid-time column. If no value is specified for the valid-time column in the positional assignment list, its value is set to the default value of the valid-time column.
  • The valid-time column name can be specified in a named list or assignment list to specify a value. If the named list or assignment list does not specify a valid-time column, the valid-time value is set to its default value.
If a SEQUENCED VALIDTIME INSERT specifies a value for the valid-time column, the period must not be NULL. To insert a row that has NULL in the valid-time column, use a NONSEQUENCED VALIDTIME INSERT statement.

For a target table with valid time and an INSERT SELECT statement:

  • The SELECT subquery must reference a table that has a valid-time column.
  • Do not specify the valid-time column or value in the select list. If the target is a bitemporal table, do not specify the valid-time or transaction-time column names or values in the select list. The values from the SELECT list are positionally assigned to the corresponding columns in the target table as if valid-time and transaction-time columns do not exist in the target.
  • The SELECT statement is executed as a sequenced SELECT if at least one table is a table with valid time. The result rows of a SELECT are inserted into the target table with their valid time period set to the overlap (P_INTERSECT) of the source row period of validity with the SELECT statement period of applicability. The precision of the resulting overlapped valid-time value must be assignable to the valid-time period of the target table.
  • If a valid-time column in the target table is a derived period column, the beginning and end bounds of the inserted rows are assigned to the component columns of the derived period valid-time column in the target table.