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.
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:
The authorization ID of the statement has the INSERT privilege on a table when:
The authorization ID of the statement has the INSERT privilege on a view when: 44
If a subselect is specified, the privileges held by the authorization ID of the statement must also include one of the following:
The authorization ID of the statement has the SELECT privilege on a table when:
The authorization ID of the statement has the SELECT privilege on a view when:
>>-INSERT INTO----+-table-name-+-------------------------------->
'-view-name--'
>-----+----------------------------+---------------------------->
| .-,--------------. |
| V | |
'-(-----column-name---+---)--'
.-,--------------------------------------------------.
V | (1)
>-----+--+-VALUES--(----+-constant--------------------------------------+--+---)-------+---+-------------------+-+>
| | +-host-variable---------------------------------+ | '-isolation-clause--' |
| | +-special-register------------------------------+ | |
| | +-NULL------------------------------------------+ | |
| | +-DEFAULT---------------------------------------+ | |
| | +-DLVALUE--(--arguments--)----------------------+ | |
| | '-cast-function-name--(--+-constant------+---)--' | |
| | '-host-variable-' | |
| '-insert-multiple-rows--------------------------------------------------------' |
'-+------------------+-------------------------------------------------------------------------------------'
'-select-statement-'
>--------------------------------------------------------------><
insert-multiple-rows
|---+-integer-------+------------------------------------------->
'-host-variable-'
>----ROWS--VALUES--(--host-structure-array--)-------------------|
Notes:
|
A value cannot be inserted into a view column that is derived from:
If 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 these columns.
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. 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.
The number of values in the VALUES clause must equal the number of names in the column list. The first value is inserted in the first column in the list, the second value in the second column, and so on.
Each host variable in the clause must identify a host structure or host variable that is declared in accordance with the rules for declaring host structures and host 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 an explanation of host-variable, see "Language Elements".
| Column Data Type | Cast Function Name | ||
|---|---|---|---|
| Distinct type N based on a BLOB, CLOB, or DBCLOB | BLOB, CLOB, or DBCLOB * | ||
| Distinct type N based on a DATE, TIME, or TIMESTAMP | DATE, TIME, or TIMESTAMP * | ||
| BLOB, CLOB, or DBCLOB | BLOB, CLOB, or DBCLOB * | ||
| DATE, TIME, or TIMESTAMP | DATE, TIME, or TIMESTAMP * | ||
| |||
The select-statement may produce values by combining two result tables with the UNION or UNION ALL operators. There can be one, more than one, or zero rows inserted when using the select-statement. If no rows are inserted, SQLCODE is set to +100 and SQLSTATE is set to '02000'.
When the base object of the INSERT and a base object of any subselect in the select statement 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 in the column 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. For an explanation of select-statement, see "select-statement".
The number of variables in the host structure must equal the number of names in the column-list. 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 in C, C++, COBOL, PL/I, and RPG".
The unique indexes and unique constraints are effectively checked at the end of the statement unless COMMIT(*NONE) was specified. In the case of a multiple-row insert, this would occur after all rows were inserted and any associated triggers were fired. If COMMIT(*NONE) is specified, checking is performed as each row is inserted.
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.
The check constraints are effectively checked at the end of the statement. In the case of a multiple-row insert, this would occur after all rows were inserted.
If a view is identified, the inserted rows must conform to any applicable WITH CHECK OPTION. For more information, see "CREATE VIEW".
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, this would occur after all rows were inserted and any associated triggers were fired.
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 made during the execution of the statement are backed out. However, other changes in the unit of work made prior to the error are not backed out. If COMMIT(*NONE) is specified, changes are not backed out.
After executing an INSERT statement, the value of SQLERRD(3) of the SQLCA is the number of rows that the database manager inserted.
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 the Database Programming book.
A maximum of 4000000 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.
Host 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.
Insert a new department with the following specifications into the DEPARTMENT table:
INSERT INTO DEPARTMENT
VALUES ('E31', 'ARCHITECTURE', '00390', 'E01')
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')
Create a temporary table MA_EMP_ACT with the same columns as the EMP_ACT table. Load MA_EMP_ACT with the rows from the EMP_ACT table with a project number (PROJNO) starting with the letters 'MA'.
CREATE TABLE MA_EMP_ACT
(EMPNO CHAR(6) NOT NULL,
PROJNO CHAR(6) NOT NULL,
ACTNO SMALLINT NOT NULL,
EMPTIME DEC(5,2),
EMSTDATE DATE,
EMENDATE DATE )
INSERT INTO MA_EMP_ACT
SELECT * FROM EMP_ACT
WHERE SUBSTR(PROJNO, 1, 2) = 'MA'
Use a PL/I program statement to add a skeleton project to the PROJECT table. 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.
EXEC SQL INSERT INTO PROJECT (PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTDATE)
VALUES (:PRJNO, :PRJNM, :DPTNO, :REMP, CURRENT DATE);
In a PL/I program, use a blocked 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 CORPDATA.DEPARTMENT 10 ROWS VALUES (:DEPT);
Insert a new project into the EMP_ACT table using the Read Uncommitted (UR, CHG) option:
INSERT INTO EMP_ACT
VALUES ('000140', 'PL2100', 30)
WITH CHG