INSERT

There are three 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.

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
• Administrative 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
• Administrative authority

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

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

(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 indicator variables are not enabled, a view column that is not updatable must not be identified. If extended indicator variables are not enabled and the object of the insert operation is a view with such 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 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 column that is defined as GENERATED ALWAYS is used. A system-generated value is not inserted.

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 cannot be specified for a ROWID, identity, or row change timestamp 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 an aggregate function or column name. If expression is a variable, the variable can identify a structure. If extended indicator variables are enabled and the expression is not a single variable, the extended indicator variable 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 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.
• If the column is a ROWID or identity column, the database manager will generate a new value.
• If the column is defined as a row change timestamp column, a new row change timestamp value is assigned to the column.

DEFAULT must be specified for a ROWID or an identity 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-DB2 for i 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.

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

Extended indicator variables: 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 must be assigned the extended indicator variable value of UNASSIGNED, unless it is an identity column defined as GENERATED ALWAYS. If the target column is an identity column defined as GENERATED ALWAYS; then it must be assigned the DEFAULT keyword or the extended indicator variable values of DEFAULT or UNASSIGNED.

Extended indicator variables and insert triggers: No change in the activation of insert triggers results from the presence of extended indicator variables. If all columns in the implicit or explicit column list have been assigned to an extended indicator variable-based 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 indicator variables and deferred error checks: When extended indicator variables 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.

Row change timestamp columns: A row change timestamp 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 timestamp value associated with the INSERT will be inserted into this column.

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

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

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

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.

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.

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.

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

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.