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.
- INSERT privilege on the target table, view, or nickname
- CONTROL privilege on the target table, view, or nickname
- INSERTIN privilege on the schema containing the target table, view, or nickname
- DATAACCESS authority on the schema containing the target table, view, or nickname
- DATAACCESS authority
- SELECT privilege
- CONTROL privilege
- SELECTIN privilege on the schema containing the table, view or nickname
- DATAACCESS authority on the schema containing the table, view, or nickname
- DATAACCESS authority
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.
- INTO table-name, view-name, nickname, or (fullselect)
- Identifies the object of the insert operation. The
name must identify one of the following objects:
- A table, view or nickname that exists at the application server
- A table or view at a remote server specified using a remote-object-name
If the object of the insert operation is a fullselect, the fullselect must be insertable, as defined in the
Insertable viewsNotes 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 no INSTEAD OF trigger exists for the insert operation on this view, a value cannot be inserted into a view column that is derived from the following elements:
- A constant, expression, or scalar function
- The same base table column as some other column of the view
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).
- Specifies the columns for which insert values are provided. Each
name must identify a column of the specified table, view, or nickname,
or a column in the fullselect. The same column must not be identified
more than once. If extended indicator variables
are not enabled, a column that cannot accept inserted values (for
example, a column based on an expression) must not be identified.
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.
- 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 at the end of the list of columns that are specified for table-name or view-name.
- 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.
- 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 (SQLSTATE 42711).
- Specifies the data type of the include column. The data type must be one that is supported by the CREATE TABLE statement.
- Introduces one or more rows of values to be inserted.
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.
- An expression
can be any expression defined in the
Expressionstopic. If expression is a row type, it must not appear in parentheses. If expression is a variable, the host variable can include an indicator variable or in the case of a host structure, an indicator array, enabled for extended indicator variables. If extended indicator variables are enabled, the extended indicator variable values of default (-5) or unassigned (-7) must not be used (SQLSTATE 22539) if either of the following statements is true:
- The expression is more complex than a single host variable with explicit casts
- The target column has data type of structured type
- Specifies the null value and should only be specified for nullable columns.
- Specifies that the default value is to be used. The result of specifying DEFAULT depends on how
the column was defined, as follows:
- If the column was defined as a generated column based on an expression, the column value is generated by the system, based on that expression.
- If the IDENTITY clause is used, the value is generated by the database manager.
- If the ROW CHANGE TIMESTAMP clause is used, the value for each inserted row is generated by the database manager as a timestamp that is unique for the table partition within the database partition.
- If the WITH DEFAULT clause is used, the value inserted is as defined for the column (see
- If the NOT NULL clause is used and the GENERATED clause is not used, or the WITH DEFAULT clause is not used or DEFAULT NULL is used, the DEFAULT keyword cannot be specified for that column (SQLSTATE 23502).
- When inserting into a nickname, the DEFAULT keyword will be passed through the INSERT statement to the data source only if the data source supports the DEFAULT keyword in its query language syntax.
- Specifies any row expression of the type described in "Row expressions" that does not include a column name. The number of fields in the row must match the target of the insert and each field must be assignable to the corresponding column.
- WITH common-table-expression
- Defines a common table expression for use with the fullselect that follows.
- Specifies a set of new rows in the form of the result table of
a fullselect. There may be one, more than one, or none. If the result
table is empty, SQLCODE is set to +100 and SQLSTATE is set to '02000'.
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.
- Specifies the
isolation level at which the statement is executed.
- Repeatable Read
- Read Stability
- Cursor Stability
- Uncommitted Read
- Triggers: INSERT statements may cause triggers to be executed. A trigger may cause other statements to be executed, or may raise error conditions based on the inserted values. If an insert operation into a view causes an INSTEAD OF trigger to fire, validity, referential integrity, and constraints will be checked against the updates that are performed in the trigger, and not against the view that caused the trigger to fire, or its underlying tables.
- Default values: The value inserted in any column that is not in the column list is either the default value of the column or null. Columns that do not allow null values and are not defined with NOT NULL WITH DEFAULT must be included in the column list. Similarly, if you insert into a view, the value inserted into any column of the base table that is not in the view is either the default value of the column or null. Hence, all columns of the base table that are not in the view must have either a default value or allow null values. The only value that can be inserted into a generated column defined with the GENERATED ALWAYS clause is DEFAULT (SQLSTATE 428C9).
- Length: If the insert value of a column is a number, the column must be a numeric column with the capacity to represent the integral part of the number. If the insert value of a column is a string, the column must either be a string column with a length attribute at least as great as the length of the string, or a datetime column if the string represents a date, time, or timestamp.
- Assignment: Insert values are assigned to columns in accordance with specific assignment rules.
- Validity: If the table named, or the base table
of the view named, has one or more unique indexes, each row inserted
into the table must conform to the constraints imposed by those indexes.
If a view whose definition includes WITH CHECK OPTION is named, each
row inserted into the view must conform to the definition of the view.
For an explanation of the rules governing this situation, see
- Referential integrity: For each constraint defined on a table, each non-null insert value of the foreign key must be equal to a primary key value of the parent table.
- Check constraint: Insert values must satisfy the check conditions of the check constraints defined on the table. An INSERT to a table with check constraints defined has the constraint conditions evaluated once for each row that is inserted.
- XML values: A value that is inserted into an XML column must be a well-formed XML document (SQLSTATE 2200M).
- Security policy: If the identified table or the
base table of the identified view is protected with a security policy,
the session authorization ID must have the label-based access control
(LBAC) credentials that allow:
- Write access to all protected columns for which a data value is explicitly provided (SQLSTATE 42512)
- Write access for any explicit value provided for a DB2SECURITYLABEL column for security policies that were created with the RESTRICT NOT AUTHORIZED WRITE SECURITY LABEL option (SQLSTATE 23523)
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:
- A value for the DB2SECURITYLABEL column is not explicitly provided
- A value for the DB2SECURITYLABEL column is explicitly provided but the session authorization ID does not have write access for that value, and the security policy is created with the OVERRIDE NOT AUTHORIZED WRITE SECURITY LABEL option
- Extended indicator variable usage: If enabled, negative indicator variable values outside the range of -1 through -7 must not be input (SQLSTATE 22010). Also, if enabled, the default and unassigned extended indicator variable values must not appear in contexts in which they are not supported (SQLSTATE 22539).
indicator variables: In an INSERT statement, a value of unassigned
has the effect of setting the column to its default value.
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).
- After execution of an INSERT statement, the value of the third variable of the SQLERRD(3) portion of the SQLCA indicates the number of rows that were passed to the insert operation. In the context of an SQL procedure statement, the value can be retrieved using the ROW_COUNT variable of the GET DIAGNOSTICS statement. SQLERRD(5) contains the count of all triggered insert, update and delete operations.
- Unless appropriate locks already exist, one or more exclusive
locks are acquired at the execution of a successful INSERT statement.
Until the locks are released, an inserted row can only be accessed
- The application process that performed the insert.
- Another application process using isolation level UR through a read-only cursor, SELECT INTO statement, or subselect used in a subquery.
- For further information about locking, see the description of the COMMIT, ROLLBACK, and LOCK TABLE statements.
an application is running against a partitioned database, and it is
bound with option INSERT BUF, then INSERT with VALUES statements which
are not processed using EXECUTE IMMEDIATE may be buffered. It is
assumed that such an INSERT statement is being processed inside a
loop in the application's logic. Rather than execute the statement
to completion, it attempts to buffer the new row values in one or
more buffers. As a result the actual insertions of the rows into
the table are performed later, asynchronous with the application's
INSERT logic. Be aware that this asynchronous insertion may cause
an error related to an INSERT to be returned on some other SQL statement
that follows the INSERT in the application.
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.
a row is inserted into a table that has an identity column, a value
is generated for the identity column.
- For a GENERATED ALWAYS identity column, the value is always generated.
- For a GENERATED BY DEFAULT column, if a value is not explicitly specified (with a VALUES clause, or subselect), a value is generated.
- When a value is inserted for a user-defined distinct type identity column, the entire computation is done in the source type, and the result is cast to the distinct type before the value is actually assigned to the column. (There is no casting of the previous value to the source type before the computation.)
inserting into a GENERATED ALWAYS identity column, a value is always
generated for the column, and users must not specify a value at insertion
time. If a GENERATED ALWAYS identity column is listed in the column-list
of the INSERT statement, with a non-DEFAULT value in the VALUES clause,
an error occurs (SQLSTATE 428C9). For example, assuming that EMPID is defined as an identity column that is GENERATED ALWAYS, then the command:
will result in an error.
INSERT INTO T2 (EMPID, EMPNAME, EMPADDR) VALUES (:hv_valid_emp_id, :hv_name, :hv_addr)
- When inserting into a GENERATED ALWAYS ROW CHANGE TIMESTAMP column, a value is always generated for the column, and users must not specify a value at insertion time (SQLSTATE 428C9) . The value generated is unique for each row inserted on the database partition.
inserting into a GENERATED BY DEFAULT column, you can specify an actual
value for the column within the VALUES clause, or from a subselect.
However, when a value is specified in the VALUES clause, the database
manager does not perform any verification of the value. To guarantee
uniqueness of IDENTITY column values, a unique index on the
identity column must be created.When inserting into a table with a GENERATED BY DEFAULT identity column, without specifying a column list, the VALUES clause can specify the DEFAULT keyword to represent the value for the identity column. In such cases, the value for the identity column will be generated.
In this example, EMPID is defined as an identity column, and thus the value inserted into this column is generated by the database manager.
INSERT INTO T2 (EMPID, EMPNAME, EMPADDR) VALUES (DEFAULT, :hv_name, :hv_addr)
- The rules for inserting into an identity column with a subselect
are similar to those for an insert with a VALUES clause. A value
for an identity column may only be specified if the identity column
is defined as GENERATED BY DEFAULT. For example, assume T1 and T2 are tables with the same definition, both containing columns intcol1 and identcol2 (both are type INTEGER and the second column has the identity attribute). Consider the following insert:
This example is logically equivalent to:
INSERT INTO T2 SELECT * 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,identcol2) SELECT intcol1, identcol2 FROM T1If there is a table with a column defined as a GENERATED ALWAYS identity, it is still possible to propagate all other columns from a table with the same definition. For example, given the example tables T1 and T2 described previously, the intcol1 values from T1 to T2 can be propagated with the following SQL:
Note that, because identcol2 is not specified in the column-list, it will be filled in with its default (generated) value.
INSERT INTO T2 (intcol1) SELECT intcol1 FROM T1
inserting a row into a single column table where the column is defined
as a GENERATED ALWAYS identity column or a ROW CHANGE TIMESTAMP column,
it is possible to specify a VALUES clause with the DEFAULT keyword.
In this case, the application does not provide any value for the table,
and the database manager generates the value for the identity or ROW
CHANGE TIMESTAMP column.
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)
INSERT INTO IDTABLE VALUES (DEFAULT), (DEFAULT), (DEFAULT), (DEFAULT)
a value for an identity column is generated, that generated value
is consumed; the next time that a value is needed, a new value is
generated. This is true even when an INSERT statement involving an
identity column fails or is rolled back.
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. The next value for the identity column will be generated, and it is possible that this next value will be unique, and that this INSERT statement will be successful.
- If the maximum value for the identity column is exceeded (or
minimum value for a descending sequence) in generating a value for
an identity column, an error occurs (SQLSTATE 23522). In this situation,
the user would have to DROP and CREATE a new table with an identity
column having a larger range (that is, change the data type or increment
value for the column to allow for a larger range of values).
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 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.
- Extended indicator variables and insert triggers: No change in the activation of insert triggers results from use 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 otherwise be done in statement preparation, to recognize an insert into a non-updatable column, is deferred until statement execution. Whether an error should be reported can be determined only during execution.
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 data type of 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.
into application-period temporal tables: An error is returned
when a row is inserted into an application-period temporal table and
the following conditions are met:
- The application-period temporal table has either a primary key or unique constraint with the BUSINESS_TIME WITHOUT OVERLAPS clause defined, or a unique index with the BUSINESS_TIME WITHOUT OVERLAPS clause defined.
- The period defined by the begin and end columns of the BUSINESS_TIME period overlap the period defined by the begin and end columns of the BUSINESS_TIME period for another row that matches the other columns of the same unique constraint or unique index.
- Considerations for an INSERT without a column list: An INSERT statement without a column list does not include implicitly hidden columns. Columns that are defined as implicitly hidden and not null must have a defined default value.
- 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: Insert two new departments using one statement
into the DEPARTMENT table as in example 2, but do not assign a manager
to the new department.
INSERT INTO DEPARTMENT (DEPTNO, DEPTNAME, ADMRDEPT) VALUES ('B11', 'PURCHASING', 'B01'), ('E41', 'DATABASE ADMINISTRATION', 'E01')
- Example 4: 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
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'
- Example 5: Use a C 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);
- Example 6: 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 NEW TABLE (INSERT INTO ORDERS(CUSTNO)INCLUDE (INSERTNUM INTEGER) VALUES(:CNUM1, 1), (:CNUM2, 2)) InsertedOrders ORDER BY INSERTNUM;
- Example 7: Use a C program statement to add a document
to the DOCUMENTS table. Obtain values for the document ID (DOCID)
column and the document data (XMLDOC) column from a host variable
that binds to an SQL TYPE IS XML AS BLOB_FILE.
EXEC SQL INSERT INTO DOCUMENTS (DOCID, XMLDOC) VALUES (:docid, :xmldoc)
- Example 8: For the following INSERT statements, assume
that table SALARY_INFO is defined with three columns, and that the
last column is an implicitly hidden ROW CHANGE TIMESTAMP column. In
the following statement, the implicitly hidden column is explicitly
referenced in the column list and a value is provided for it in the
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 (LEVEL, SALARY, UPDATE_TIME) VALUES (2, 30000, CURRENT TIMESTAMP)
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.
INSERT INTO SALARY_INFO VALUES (2, 30000)