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.
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).