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