INSERT

The INSERT statement inserts rows into a table or view. Inserting a row into a view also inserts the row into the table on which the view is based if no INSTEAD OF INSERT trigger is defined on this view. If such a trigger is defined, the trigger will be activated instead.

There are four forms of this statement:

The INSERT using VALUES form is used to insert one or more rows into the table or view using the values provided or referenced.
The INSERT using fullselect form is used to insert one or more rows into the table or view using values from other tables or views.
The INSERT using n ROWS form is used to insert multiple rows into the table or view using the values provided in a host-structure-array.
The INSERT DEFAULT VALUES form is used to insert a single row using default values for every column.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared with the exception of the n ROWS form, which must be a static statement embedded in an application program. The n ROWS form is not allowed in a REXX procedure.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

For the table or view identified in the statement:
- The INSERT privilege on the table or view, and
- The system authority *EXECUTE on the library containing the table or view
Database administrator authority

If a fullselect is specified, the privileges held by the authorization ID of the statement must also include one of the following:

For each table or view identified in the fullselect:
- The SELECT privilege on the table or view, and
- The system authority *EXECUTE on the library containing the table or view
Database administrator authority

For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View.

Syntax

insert-specification

include-columns

insert-multiple-rows

isolation–clause

lock-clause

data-type

built-in-type

allocate-clause

ccsid-clause

normalize-clause

Description

INTO table-name or view-name

Identifies the object of the insert operation. The name must identify a table or view that exists at the current server, but it must not identify Start of change

a history table, End of change

a catalog table, a view of a catalog table, or a view that is not insertable. For an explanation of insertable views, see CREATE VIEW.

DEFAULT VALUES

Specifies that a default value will be assigned to every column in the table. This is the same as specifying a list of values for all the columns with each of the values being the keyword DEFAULT. The value that is inserted depends on how the column was defined, as described for the DEFAULT keyword. End of change

(column-name,…)

Specifies the columns for which insert values are provided. Each name must be a name that identifies a column of the table or view. The same column must not be identified more than once. If extended indicators are not enabled, a view column that is not updatable must not be identified. If extended indicators are not enabled and the object of the insert operation is a view with non-updatable columns, a list of column names must be specified and the list must not identify those columns. For an explanation of updatable columns in views, see CREATE VIEW.

Omission of the column list is an implicit specification of a list in which every column of the table or view is identified in left-to-right order. Any columns defined with the hidden attribute are omitted. This list is established when the statement is prepared and, therefore, does not include columns that were added to a table after the statement was prepared.

If the INSERT statement is embedded in an application and the referenced table or view exists at create program time, the statement is prepared at create program time. Otherwise, the statement is prepared at the first successful execute of the INSERT statement.

include-columns

Specifies a set of columns that are included, along with the columns of table-name or view-name, in the intermediate result table of the INSERT statement when it is nested in the FROM clause of a fullselect. The include-columns are appended to the end of the list of columns specified by table-name or view-name.

INCLUDE: Specifies a list of columns to be included in the intermediate result table of the INSERT statement. This clause can only be specified if the INSERT statement is nested in the FROM clause of a fullselect.
column-name: Specifies a column of the intermediate result table of the INSERT statement. The name cannot be the same as the name of another include column or a column in table-name or view-name.
FOR COLUMN system-column-name: Provides an IBM® i name for the column. The name must not be the same as any column-name or system-column-name in the INCLUDE column list or in table-name or view-name.
data-type: Specifies the data type of the include column. For a description of data-type, see CREATE TABLE. If a DATALINK data-type is used, FILE LINK CONTROL is not allowed.

OVERRIDING SYSTEM VALUE or OVERRIDING USER VALUE

Specifies whether system generated values or user-specified values for a ROWID, identity, or row change timestamp column are used. If OVERRIDING SYSTEM VALUE is specified, the implicit or explicit list of columns for the INSERT statement must contain a Start of change

ROWID, identity, or row change timestamp End of change

column defined as GENERATED ALWAYS. If OVERRIDING USER VALUE is specified, the implicit or explicit list of columns for the INSERT statement must contain a column defined as either GENERATED ALWAYS or GENERATED BY DEFAULT.

OVERRIDING SYSTEM VALUE: Specifies that the value specified in the VALUES clause or produced by a fullselect for a ROWID, identity, or row change timestamp column that is defined as GENERATED ALWAYS is used. A system-generated value is not inserted.; If a value for a row-begin, row-end, transaction-start-ID, or generated expression column is provided, it must be DEFAULT.
OVERRIDING USER VALUE: Specifies that the value specified in the VALUES clause or produced by a fullselect for a column that is defined as either GENERATED ALWAYS or GENERATED BY DEFAULT is ignored. Instead, a system-generated value is inserted, overriding the user-specified value.

If neither OVERRIDING SYSTEM VALUE nor OVERRIDING USER VALUE is specified:

A value other than DEFAULT cannot be specified for a ROWID, identity, row change timestamp, row-begin, row-end, transaction-start-ID, or generated expression column that is defined as GENERATED ALWAYS.
A value can be specified for a ROWID, identity, or row change timestamp column that is defined as GENERATED BY DEFAULT. If a value is specified that value is assigned to the column. However, a value can be inserted into a ROWID column defined BY DEFAULT only if the specified value is a valid row ID value that was previously generated by Db2® for z/OS® or Db2 for i. When a value is inserted into an identity or row change timestamp column defined BY DEFAULT, the database manager does not verify that the specified value is a unique value for the column unless the identity or row change timestamp column is the sole key in a unique constraint or unique index. Without a unique constraint or unique index, the database manager can guarantee unique values only among the set of system-generated values as long as NO CYCLE is in effect.
If a value is not specified the database manager generates a new value.

VALUES

Specifies one or more new rows to be inserted.

Each variable in the clause must identify a host structure or variable that is declared in accordance with the rules for declaring host structures and variables. In the operational form of the statement, a reference to a host structure is replaced by a reference to each of its variables. For further information about variables and structures, see References to host variables and Host structures.

The number of values for each row in the VALUES clause must equal the number of names in the implicit or explicit column list and the columns identified in the INCLUDE clause. The first value is inserted in the first column in the list, the second value in the second column, and so on.

expression

An expression of the type described in Expressions, that does not include a column name. If expression is a variable, the variable can identify a structure. If extended indicators are enabled and the expression is not a single variable, the extended indicator values of DEFAULT and UNASSIGNED must not be used for that expression.

DEFAULT

Specifies that the default value is assigned to a column. The value that is inserted depends on how the column was defined, as follows:

If the column is defined as a generated column based on an expression, the column value is generated by the database manager, based on that expression.
If the column is a ROWID, an identity column, a row change timestamp column, a row-begin column, a row-end column, or a transaction-start-ID column, the database manager will generate a new value.
If the WITH DEFAULT clause is used, the default inserted is as defined for the column (see default-clause in column-definition in CREATE TABLE).
If the WITH DEFAULT clause or the NOT NULL clause is not used, the value inserted is NULL.
If the NOT NULL clause is used and the WITH DEFAULT clause is not used or DEFAULT NULL is used, the DEFAULT keyword cannot be specified for that column.

DEFAULT must be specified for a ROWID or Start of change a generated End of change column that was defined as GENERATED ALWAYS unless OVERRIDING USER VALUE is specified to indicate that any user-specified value will be ignored and a unique system-generated value will be inserted.

NULL

Specifies the value for a column is the null value. NULL should only be specified for nullable columns.

WITH common-table-expression

Specifies a common table expression. For an explanation of common table expression, see common-table-expression.

fullselect

Specifies a set of new rows in the form of the result table of a fullselect. If the result table is empty, SQLSTATE is set to '02000'.

For an explanation of fullselect, see fullselect.

When the base object of the INSERT and a base object of any subselect in the fullselect are the same table, the select statement is completely evaluated before any rows are inserted.

The number of columns in the result table must equal the number of names implicitly or explicitly specified in the column-name list. The value of the first column of the result is inserted in the first column in the list, the second value in the second column, and so on.

isolation-clause

Specifies the isolation level to be used for this statement.

WITH

Introduces the isolation level, which may be one of:

RR Repeatable read
RS Read stability
CS Cursor stability
UR Uncommitted read
NC No commit

If isolation-clause is not specified the default isolation is used. See isolation-clause for a description of how the default is determined.

insert-multiple-rows

integer or variable ROWS

Specifies the number of rows to be inserted. If a variable is specified, it must be numeric with zero scale and cannot include an indicator variable.

VALUES (host-structure-array)

Specifies a set of new rows in the form of an array of host structures. The host-structure-array must be declared in the program in accordance with the rules for declaring host structure arrays. A parameter marker may not be used in place of the host-structure-array name.

The number of variables in the host structure must equal the number of names in the implicit or explicit column list and the columns identified in the INCLUDE clause. The first host structure in the array corresponds to the first row, the second host structure in the array corresponds to the second row, and so on. In addition, the first variable in the host structure corresponds with the first column of the row, the second variable in the host structure corresponds with the second column of the row, and so on.

For an explanation of arrays of host structures see Host structure arrays.

insert-multiple-rows is not allowed if the current connection is to a non-remote server. insert-multiple-rows is not allowed in a data change reference in an RPG/400® or PL/I program.

INSERT Rules

Default Values: The value inserted in any column that is not in the column list is the default value of the column. Columns without a default value must be included in the column list. Similarly, if you insert into a view without an INSTEAD OF INSERT trigger, the default value is inserted into any column of the base table that is not included in the view. Hence, all columns of the base table that are not in the view must have a default value.

Assignment: Insert values are assigned to columns in accordance with the storage assignment rules described in Language elements.

Validity: Insert operations must obey the following rules. If they do not, or if any other errors occur during the execution of the INSERT statement, no rows are inserted unless COMMIT(*NONE) was specified.

Unique constraints and unique indexes: If the identified table, or the base table of the identified view, has one or more unique indexes or unique constraints, each row inserted into the table must conform to the limitations imposed by those indexes and constraints (SQLSTATE 23505).
All uniqueness checks are effectively made at the end of the statement unless COMMIT(*NONE) was specified. In the case of a multiple-row INSERT statement, this would occur after all rows were inserted. If COMMIT(*NONE) is specified, checking is performed as each row is inserted.
Check constraints: If the identified table, or the base table of the identified view, has one or more check constraints, each check constraint must be true or unknown for each row inserted into the table (SQLSTATE 23513).
The check constraints are effectively checked at the end of the statement. In the case of a multiple-row INSERT statement, this would occur after all rows were inserted.
Views and the CHECK OPTION clause: If a view is identified, the inserted rows must conform to any applicable CHECK OPTION clause (SQLSTATE 44000). For more information, see CREATE VIEW.

Triggers: If the identified table or the base table of the identified view has an insert trigger, the trigger is activated. A trigger might cause other statements to be executed or raise error conditions based on the insert values. If the INSERT statement is used as a data-change-table-reference, an AFTER INSERT trigger that attempts to modify the inserted rows will cause an error.

Referential Integrity: Each nonnull insert value of a foreign key must equal some value of the parent key of the parent table in the relationship.

The referential constraints (other than a referential constraint with a RESTRICT delete rule) are effectively checked at the end of the statement. In the case of a multiple-row INSERT statement, this would occur after all rows were inserted and any associated triggers were activated.

If the INSERT statement is used as a data-change-table-reference, any referential constraint that attempts to modify the inserted rows will cause an error.

XML values: A value that is inserted into an XML column must be a well-formed XML document.

Inserting rows into a table for which row or column access control is enforced: When an INSERT statement is issued for a table for which row or column access control is enforced, the rules specified in the enabled row permissions or column masks determine whether the row can be inserted. Typically those rules are based on the authorization ID of the statement. The following rules describe how the enabled row permissions and column masks are used during INSERT:

When a column is referenced while deriving the values of a new row, if that column has an enabled column mask, the masked value is used to derive the new values. If the object table also has column access control activated, the column mask that is applied to derive the new values must return the column itself, not a constant or an expression. If the column mask does not mask the column to itself, the new value cannot be used for insert and an error is returned.
If the OVERRIDING USER VALUE clause is specified, the corresponding values in the new row are ignored, and the above rule for column masks is not applicable to those values.
If the row can be inserted, and there is a BEFORE INSERT trigger for the table, the trigger is activated.
Within the trigger actions, the new values for insert can be modified in the transition variables. When the values return from the trigger, the final values for the new values are the ones for insert.
A row to be inserted must conform to the enabled row permissions.
When multiple enabled row permissions are defined for a table, a row access control search condition is derived by application of the logical OR operator to the search condition in each enabled row permission. A row that conforms to all enabled row permissions is a row that, if the row is inserted, can be retrieved back using the row access control search condition.
If the rows can be inserted and there is an AFTER INSERT trigger for the table, the trigger is activated.

The preceding rules do not apply to the include-columns. The include-columns will accept whatever values are assigned to them, which could be either masked or unmasked values.

Masked data can be assigned to a variable used as a value for the insert operation. If an insert violation check constraint does not exist for the column, the masked data will be inserted in the column and no error will be issued.

Extended indicator usage: When extended indicators are enabled, indicator values other than positive values and 0 (zero) through -7 must not be specified. The DEFAULT and UNASSIGNED extended indicator values must not appear in contexts where they are not supported.

Extended indicators: In an INSERT statement, the extended indicator value of UNASSIGNED has the effect of setting the column to its default value. If a target column is not updatable, it can only be assigned the extended indicator value of UNASSIGNED, unless it is a Start of change generated column End of change defined as GENERATED ALWAYS. If the target column is a generated column defined as GENERATED ALWAYS, then it must be assigned the extended indicator value of DEFAULT or UNASSIGNED.

Extended indicators and insert triggers: The activation of insert triggers is not affected by the use of extended indicators. If all columns in the implicit or explicit column list have been assigned an extended indicator value of UNASSIGNED or DEFAULT, an insert where all columns have their respective default values is attempted and, if successful, the insert trigger is activated.

Extended indicators and deferred error checks: When extended indicators are enabled, validation that would normally be done during statement preparation to recognize an insert into a non-updatable column is deferred until the statement is executed.

Notes

Insert operation errors: If an insert value violates any constraints, or if any other error occurs during the execution of an INSERT statement and COMMIT(*NONE) was not specified, all changes from this statement and any triggered SQL statements are rolled back. However, other changes in the unit of work made prior to the error are not rolled back. If COMMIT(*NONE) is specified, changes are not rolled back.

Number of rows inserted: After executing an INSERT statement, the ROW_COUNT statement information item in the SQL Diagnostics Area (or SQLERRD(3) of the SQLCA) is the number of rows that the database manager inserted. The ROW_COUNT item does not include the number of rows that were inserted as a result of a trigger.

For a description of ROW_COUNT, see GET DIAGNOSTICS. For a description of the SQLCA, see SQLCA (SQL communication area).

Locking: If COMMIT(*RR), COMMIT(*ALL), COMMIT(*CS), or COMMIT(*CHG) is specified, one or more exclusive locks are acquired during the execution of a successful INSERT statement. Until the locks are released by a commit or rollback operation, an inserted row can only be accessed by:

The application process that performed the insert
Another application process using COMMIT(*NONE) or COMMIT(*CHG) through a read-only operation

The locks can prevent other application processes from performing operations on the table. For further information about locking, see the description of the COMMIT, ROLLBACK, and LOCK TABLE statements. Also, see Isolation level and Database Programming.

If the INSERT is used as a data-change-table-reference where FINAL TABLE is specified, locks are placed on inserted rows until the SELECT is complete. These locks may prevent indirect changes to the inserted rows from within the same job, such as an AFTER TRIGGER attempting to change an inserted row. These locks are acquired for all isolation levels, including COMMIT(*NONE).

A maximum of 500 000 000 rows can be inserted or changed in any single INSERT statement when COMMIT(*RR), COMMIT(*ALL), COMMIT(*CS), or COMMIT(*CHG) was specified. The number of rows changed includes any rows inserted, updated, or deleted under the same commitment definition as a result of a trigger.

Start of change Generated columns: A generated End of change column that is defined as GENERATED ALWAYS should not be specified in the column-list unless the corresponding entry in the VALUES list is DEFAULT. The user can specify the OVERRIDING USER VALUE clause to indicate that any user-specified value will be ignored and the Start of change system-generated value at the time of End of change the INSERT will be inserted into this column.

Inserting into tables with row-begin, row-end, or transaction start-ID columns: When a row is inserted into a table with these generated columns (for instance, a system-period temporal table), the database manager assigns values to the following columns:

A row-begin column is assigned a value that is generated using a reading of the time-of-day clock during execution of the first data change statement in the transaction that requires a value to be assigned to the row-begin or transaction start-ID column in a table, or a row in a system-period temporal table is deleted. The database manager ensures uniqueness of the generated values for a row-begin column across transactions. If multiple rows are inserted within a single SQL transaction, the values for the row-begin column are the same for all the rows and are unique from the values generated for the column for another transaction.
A row-end column is assigned the maximum value for the column (9999-12-30-00.00.00.000000000000).
A transaction start-ID column is assigned a unique timestamp value per transaction or the null value. The null value is assigned to the transaction start-ID column if the column is nullable. Otherwise, the value is generated using a reading of the time-of-day clock during execution of the first data change statement in the transaction that requires a value to be assigned to the row-begin or transaction start-ID column in a table, or a row in a system-period temporal table is deleted. If multiple rows are inserted within a single SQL transaction, the values for the transaction start-ID column are the same for all the rows and are unique from the values generated for the column for another transaction.

Inserting into a system-period temporal table: When a row is inserted into a system-period temporal table, the database manager assigns values to columns as indicated for tables with row-begin, row-end, or transaction start-ID columns. Also, when a row is inserted, no rows are added to the history table associated with the system-period temporal table.

If the CURRENT TEMPORAL SYSTEM_TIME special register is set to a non-null value and the value of the SYSTIME option is YES, the underlying target of the INSERT statement cannot be a system-period temporal table. This restriction applies regardless of whether the system-period temporal table is directly or indirectly referenced.

REXX: Variables cannot be used in the INSERT statement within a REXX procedure. Instead, the INSERT must be the object of a PREPARE and EXECUTE using parameter markers.

Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:

The keyword NONE can be used as a synonym for NC.
The keyword CHG can be used as a synonym for UR.
The keyword ALL can be used as a synonym for RS.

Examples

Example 1: Insert a new department with the following specifications into the DEPARTMENT table:

Department number (DEPTNO) is ‘E31'
Department name (DEPTNAME) is ‘ARCHITECTURE'
Managed by (MGRNO) a person with number ‘00390'
Reports to (ADMRDEPT) department ‘E01'.

  INSERT INTO DEPARTMENT
    VALUES ('E31', 'ARCHITECTURE', '00390', 'E01')

Example 2: Insert a new department into the DEPARTMENT table as in example 1, but do not assign a manager to the new department.

  INSERT INTO DEPARTMENT (DEPTNO, DEPTNAME, ADMRDEPT)
    VALUES ('E31', 'ARCHITECTURE', 'E01')

Example 3: Create a table MA_EMPPROJACT with the same columns as the EMPPROJACT table. Populate MA_EMPPROJACT with the rows from the EMPPROJACT table with a project number (PROJNO) starting with the letters ‘MA'.

  CREATE TABLE MA_EMPPROJACT LIKE EMPPROJACT

  INSERT INTO MA_EMPPROJACT
    SELECT * FROM EMPPROJACT
      WHERE SUBSTR(PROJNO, 1, 2) = 'MA'

Example 4: Use a Java program statement to add a skeleton project to the PROJECT table on the connection context 'ctx'. Obtain the project number (PROJNO), project name (PROJNAME), department number (DEPTNO), and responsible employee (RESPEMP) from host variables. Use the current date as the project start date (PRSTDATE). Assign a NULL value to the remaining columns in the table.

  #sql [ctx] { INSERT INTO PROJECT (PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTDATE)
              VALUES (:PRJNO, :PRJNM, :DPTNO, :REMP, CURRENT DATE) };

Example 5: Insert two new departments using one statement into the DEPARTMENT table as in example 2, but do not assign a manager to the new departments.

  INSERT INTO DEPARTMENT (DEPTNO, DEPTNAME, ADMRDEPT)
    VALUES ('B11', 'PURCHASING', 'B01'),
           ('E41', 'DATABASE ADMINISTRATION', 'E01')

Example 6: In a PL/I program, use a multiple-row INSERT to add 10 rows to table DEPARTMENT. The host structure array DEPT contains the data to be inserted.

   DCL  1  DEPT(10),
           3  DEPT  CHAR(3),
           3  LASTNAME  CHAR(29)  VARYING,
           3  WORKDEPT  CHAR(6),
           3  JOB  CHAR(3);

   EXEC SQL INSERT INTO DEPARTMENT 10 ROWS VALUES (:DEPT);

Example 7: Insert a new project into the EMPPROJACT table using the Read Uncommitted (UR, CHG) option:

   INSERT INTO EMPPROJACT
     VALUES ('000140', 'PL2100', 30)
     WITH CHG

Example 8: Specify an INSERT statement as the data-change-table-reference within a SELECT statement. Define an extra include column whose values are specified in the VALUES clause, which is then used as an ordering column for the inserted rows.

   SELECT inorder, ordernum
     FROM FINAL TABLE (INSERT INTO ORDERS (CUSTNO)
                                   INCLUDE(INSERTNUM INTEGER)
                         VALUES (:cnum1, 1),
                                (:cnum2, 2)) InsertedOrders
     ORDER BY insertnum