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 three forms of this statement:
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.
The privileges held by the authorization ID of the statement must include at least one of the following:
If a fullselect is specified, the privileges held by the authorization ID of the statement must also include one of the following:
For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View.
>>-INSERT INTO--+-table-name-+--+-----------------------+-------> '-view-name--' | .-,-----------. | | V | | '-(----column-name-+--)-' >--+-----------------+--+-------------------------+-------------> '-include-columns-' +-OVERRIDING SYSTEM VALUE-+ '-OVERRIDING USER VALUE---' .-,----------------------------. V | >--+-VALUES----+-+-expression-+-----------+-+--+------------------+-------------------------+->< | | +-DEFAULT----+ | '-isolation-clause-' | | | '-NULL-------' | | | | .-,--------------. | | | | V | | | | '-(----+-expression-+-+--)-' | | +-DEFAULT----+ | | '-NULL-------' | +-insert-multiple-rows--+------------------+---------------------------------------------+ | '-isolation-clause-' | '-+--------------------------------------------------+--fullselect--+------------------+-' | .-,-----------------------. | '-isolation-clause-' | V | | '-WITH--+-----------+----common-table-expression-+-' '-RECURSIVE-' include-columns .-,---------------------------------------------------------------. V | |--INCLUDE--(----column-name--+-------------------------------------+--data-type-+--)--| | .-COLUMN-. | '-FOR--+--------+--system-column-name-' insert-multiple-rows |--+-integer--+--ROWS--VALUES--(--host-structure-array--)-------| '-variable-' isolation–clause |--WITH--+-NC------------------+--------------------------------| +-UR------------------+ +-CS--+------------+--+ | '-KEEP LOCKS-' | +-RS--+-------------+-+ | '-lock-clause-' | '-RR--+-------------+-' '-lock-clause-' lock-clause |--USE AND KEEP EXCLUSIVE LOCKS---------------------------------|
built-in-type |--+-+---SMALLINT---+----------------------------------------------------------------------------------------------------------+--| | +-+-INTEGER-+--+ | | | '-INT-----' | | | '---BIGINT-----' | | .-(5,0)------------------------. | +-+-+-DECIMAL-+-+--+------------------------------+-------------------------------------------------------------------------+ | | '-DEC-----' | | .-,0--------. | | | '-+-NUMERIC-+-' '-(--integer--+-----------+--)-' | | '-NUM-----' '-, integer-' | | .-(--53--)------. | +-+-FLOAT--+---------------+-+----------------------------------------------------------------------------------------------+ | | '-(--integer--)-' | | | +-REAL---------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+----' | | .-(--34--)-. | +---DECFLOAT--+----------+--------------------------------------------------------------------------------------------------+ | '-(--16--)-' | | .-(--1--)-------. | +-+-+-+-CHARACTER-+--+---------------+-------------------------------+--+----------------+------------+---------------------+ | | | '-CHAR------' '-(--integer--)-' | +-FOR BIT DATA---+ | | | | '-+-+-CHARACTER-+--VARYING-+--(--integer--)--+-----------------+-' +-FOR SBCS DATA--+ | | | | | '-CHAR------' | '-allocate-clause-' +-FOR MIXED DATA-+ | | | | '-VARCHAR----------------' '-ccsid-clause---' | | | | .-(--1M--)-------------. | | | '---+-CLOB-------------------+----+----------------------+--+-----------------+--+----------------+-' | | +-CHAR LARGE OBJECT------+ '-(--integer--+---+--)-' '-allocate-clause-' +-FOR SBCS DATA--+ | | '-CHARACTER LARGE OBJECT-' +-K-+ +-FOR MIXED DATA-+ | | +-M-+ '-ccsid-clause---' | | '-G-' | | .-(--1--)-------. | +-+---GRAPHIC----+---------------+----------------------------+--+--------------+-------------------------------------------+ | | '-(--integer--)-' | '-ccsid-clause-' | | +-+-GRAPHIC VARYING-+--(--integer--)--+-----------------+---+ | | | '-VARGRAPHIC------' '-allocate-clause-' | | | | .-(--1M--)-------------. | | | '---DBCLOB----+----------------------+--+-----------------+-' | | '-(--integer--+---+--)-' '-allocate-clause-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-+-NATIONAL CHARACTER-+--+---------------+-------------------------------+---------------------+--+------------------+-+ | | | +-NATIONAL CHAR------+ '-(--integer--)-' | | '-normalize-clause-' | | | | '-NCHAR--------------' | | | | | '-+-+-NATIONAL CHARACTER-+--VARYING-+--(--integer--)--+-----------------+-' | | | | | +-NATIONAL CHAR------+ | '-allocate-clause-' | | | | | '-NCHAR--------------' | | | | | '-NVARCHAR------------------------' | | | | .-(--1M--)-------------. | | | '-----+-+-NATIONAL CHARACTER-+--LARGE OBJECT-+------+----------------------+--+-----------------+-' | | | '-NCHAR--------------' | '-(--integer--+---+--)-' '-allocate-clause-' | | '-NCLOB--------------------------------' +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-BINARY--+---------------+------------------------------+-----------------+--------------------------------------------+ | | | '-(--integer--)-' | | | | | '-+-BINARY VARYING-+--(--integer--)--+-----------------+-' | | | | '-VARBINARY------' '-allocate-clause-' | | | | .-(--1M--)-------------. | | | '---+-BLOB----------------+----+----------------------+--+-----------------+-' | | '-BINARY LARGE OBJECT-' '-(--integer--+---+--)-' '-allocate-clause-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE-------------------+------------------------------------------------------------------------------------------------+ | | .-(--0--)-. | | | +-TIME--+---------+------+ | | | .-(--6--)-. | | | '-TIMESTAMP--+---------+-' | | .-(--200--)-----. | +-----DATALINK--+---------------+--+-----------------+--+--------------+----------------------------------------------------+ | '-(--integer--)-' '-allocate-clause-' '-ccsid-clause-' | +---ROWID-------------------------------------------------------------------------------------------------------------------+ '---XML--+-----------------+--+--------------+------------------------------------------------------------------------------' '-allocate-clause-' '-ccsid-clause-' allocate-clause |--ALLOCATE--(--integer--)--------------------------------------| ccsid-clause |--CCSID--integer--+------------------+-------------------------| '-normalize-clause-' normalize-clause .-NOT NORMALIZED-. |--+-NORMALIZED-----+-------------------------------------------|
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.
If neither OVERRIDING SYSTEM VALUE nor OVERRIDING USER VALUE is specified:
If a value is not specified the database manager generates a new value.
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.
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.
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.
Introduces the isolation level, which may be one of:
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.
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.
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.
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.
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: Extended indicator variables can only be enabled for INSERT with VALUES; they are not allowed with insert with fullselect or a multiple-row insert. 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.
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 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:
Example 1: Insert a new department with the following specifications into the DEPARTMENT table:
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