The INSERT statement inserts rows into a table, nickname, or view, or the underlying tables, nicknames, or views of the specified fullselect. Inserting a row into a nickname inserts the row into the data source object to which the nickname refers. Inserting a row into a view also inserts the row into the table on which the view is based, if no INSTEAD OF trigger is defined for the insert operation on this view. If such a trigger is defined, the trigger will be executed instead.
This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared.
GROUP privileges are not checked for static INSERT statements.
If the target of the insert operation is a nickname, the privileges on the object at the data source are not considered until the statement is executed at the data source. At this time, the authorization ID that is used to connect to the data source must have the privileges required for the operation on the object at the data source. The authorization ID of the statement can be mapped to a different authorization ID at the data source.
>>-INSERT INTO--+-table-name-------+----------------------------> +-view-name--------+ +-nickname---------+ '-(--fullselect--)-' >--+-----------------------+--+---------------------+-----------> | .-,-----------. | '-| include-columns |-' | V | | '-(----column-name-+--)-' .-,----------------------------. V | >--+-VALUES----+-+-expression-+-----------+-+----------+--------> | | +-NULL-------+ | | | | '-DEFAULT----' | | | | .-,--------------. | | | | V | | | | +-(----+-expression-+-+--)-+ | | | +-NULL-------+ | | | | '-DEFAULT----' | | | '-row-expression-----------' | '-+-----------------------------------+--fullselect-' | .-,-----------------------. | | V | | '-WITH----common-table-expression-+-' >--+--------------+-------------------------------------------->< '-WITH--+-RR-+-' +-RS-+ +-CS-+ '-UR-' include-columns .-,----------------------. V | |--INCLUDE--(----column-name--data-type-+--)--------------------|
If the object of the insert operation is a fullselect, the fullselect must be insertable, as defined in the "Insertable views" Notes item in the description of the CREATE VIEW statement.
If the object of the insert operation is a nickname, the extended indicator variable values of DEFAULT and UNASSIGNED must not be used (SQLSTATE 22539).
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.
A row can be inserted into a view or a fullselect that is defined using a UNION ALL if the row satisfies the check constraints of exactly one of the underlying base tables. If a row satisfies the check constraints of more than one table, or no table at all, an error is returned (SQLSTATE 23513).
A row cannot be inserted into a view or a fullselect that is defined using a UNION ALL if any base table of the view contains a before trigger and the before trigger contains an UPDATE, a DELETE, or an INSERT operation, or invokes any routine containing such operations (SQLSTATE 42987).
Omission of the column list is an implicit specification of a list in which every column of the table (that is not implicitly hidden) or view, or every item in the select-list of the fullselect 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.
Each row specified in the VALUES clause must be assignable to the implicit or explicit column list and the columns identified in the INCLUDE clause, unless a row variable is used. When a row value list in parentheses is specified, the first value is inserted into the first column in the list, the second value into the second column, and so on. When a row expression is specified, the number of fields in the row type must match the number of names in the implicit or explicit column list.
When the base object of the INSERT and the base object of the fullselect or any subquery of the fullselect, are the same table, the fullselect 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.
If the expression that specifies the value of a result column is a variable, the host variable can include an indicator variable enabled for extended indicator variables. If extended indicator variables are enabled, and the expression is more than a single host variable, or a host variable being explicitly cast, then the extended indicator variable values of default or unassigned must not be used (SQLSTATE 22539). The effects of default or unassigned values apply to the corresponding target columns of the fullselect.
The session authorization ID must also have been granted a security label for write access for the security policy if an implicit value is used for a DB2SECURITYLABEL column (SQLSTATE 23523), which can happen when:
If the target column is a column defined as GENERATED ALWAYS, then it must be assigned the DEFAULT keyword, or the extended indicator variable-based values of default or unassigned (SQLSTATE 428C9).
This has the potential to dramatically improve INSERT performance, but is best used with clean data, due to the asynchronous nature of the error handling.
INSERT INTO T2 (EMPID, EMPNAME, EMPADDR)
VALUES (:hv_valid_emp_id, :hv_name, :hv_addr)
will
result in an error. INSERT INTO T2 (EMPID, EMPNAME, EMPADDR)
VALUES (DEFAULT, :hv_name, :hv_addr)
In this example,
EMPID is defined as an identity column, and thus the value inserted
into this column is generated by DB2. INSERT INTO T2
SELECT *
FROM T1
This example is logically equivalent
to: INSERT INTO T2 (intcol1,identcol2)
SELECT intcol1, identcol2
FROM T1
In both cases, the INSERT statement
is providing an explicit value for the identity column of T2. This
explicit specification can be given a value for the identity column,
but the identity column in T2 must be defined as GENERATED BY DEFAULT.
Otherwise, an error will result (SQLSTATE 428C9). INSERT INTO T2 (intcol1)
SELECT intcol1
FROM T1
Note that, because identcol2 is not
specified in the column-list, it will be filled in with its default
(generated) value. INSERT INTO IDTABLE
VALUES(DEFAULT)
Assuming the same single column
table for which the column has the identity attribute, to insert multiple
rows with a single INSERT statement, the following INSERT statement
could be used: INSERT INTO IDTABLE
VALUES (DEFAULT), (DEFAULT), (DEFAULT), (DEFAULT)
For example, assume that a unique index has been created on the identity column. If a duplicate key violation is detected in generating a value for an identity column, an error occurs (SQLSTATE 23505) and the value generated for the identity column is considered to be consumed. This can occur when the identity column is defined as GENERATED BY DEFAULT and the system tries to generate a new value, but the user has explicitly specified values for the identity column in previous INSERT statements. Reissuing the same INSERT statement in this case can lead to success. DB2 will generate the next value for the identity column, and it is possible that this next value will be unique, and that this INSERT statement will be successful.
For example, an identity column may have been defined with a data type of SMALLINT, and eventually the column runs out of assignable values. To redefine the identity column as INTEGER, the data would need to be unloaded, the table would have to be dropped and recreated with a new definition for the column, and then the data would be reloaded. When the table is redefined, it needs to specify a START WITH value for the identity column such that the next value generated by DB2 will be the next value in the original sequence. To determine the end value, issue a query using MAX of the identity column (for an ascending sequence), or MIN of the identity column (for a descending sequence), before unloading the data.
INSERT INTO DEPARTMENT
VALUES ('E31', 'ARCHITECTURE', '00390', 'E01')
INSERT INTO DEPARTMENT (DEPTNO, DEPTNAME, ADMRDEPT )
VALUES ('E31', 'ARCHITECTURE', 'E01')
INSERT INTO DEPARTMENT (DEPTNO, DEPTNAME, ADMRDEPT)
VALUES ('B11', 'PURCHASING', 'B01'),
('E41', 'DATABASE ADMINISTRATION', 'E01')
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'
EXEC SQL INSERT INTO PROJECT (PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTDATE)
VALUES (:PRJNO, :PRJNM, :DPTNO, :REMP, CURRENT DATE);
SELECT INORDER.ORDERNUM
FROM NEW TABLE (INSERT INTO ORDERS(CUSTNO)INCLUDE (INSERTNUM INTEGER)
VALUES(:CNUM1, 1), (:CNUM2, 2)) InsertedOrders
ORDER BY INSERTNUM;
EXEC SQL INSERT INTO DOCUMENTS
(DOCID, XMLDOC) VALUES (:docid, :xmldoc)
INSERT INTO SALARY_INFO (LEVEL, SALARY, UPDATE_TIME)
VALUES (2, 30000, CURRENT TIMESTAMP)
The following INSERT statement uses an implicit column list. An implicit
column list does not include implicitly hidden columns, so the VALUES
clause only contains values for the other two columns. INSERT INTO SALARY_INFO VALUES (2, 30000)
In
this case, the UPDATE_TIME column must be defined to have a default
value, and that default value is used for the row that is inserted.