SQL Reference

INSERT

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.

Invocation

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.

Authorization

To execute this statement, the privileges held by the authorization ID of the statement must include at least one of the following:

INSERT privilege on the table or view where rows are to be inserted
CONTROL privilege on the table or view where rows are to be inserted
SYSADM or DBADM authority.

In addition, for each table or view referenced in any fullselect used in the INSERT statement, the privileges held by the authorization ID of the statement must include at least one of the following:

SELECT privilege
CONTROL privilege
SYSADM or DBADM authority.

GROUP privileges are not checked for static INSERT statements.

Syntax

>>-INSERT INTO----+-table-name-+-------------------------------->
                  '-view-name--'
 
>-----+----------------------------+---------------------------->
      |    .-,--------------.      |
      |    V                |      |
      '-(-----column-name---+---)--'
 
                .-,------------------------------------.
                V                                      |
>-----+-VALUES------+-+-expression-+----------------+--+--------+-><
      |             | +-NULL-------+                |           |
      |             | '-DEFAULT----'                |           |
      |             |    .-,-----------------.      |           |
      |             |    V                   |      |           |
      |             '-(------+-expression-+--+---)--'           |
      |                      +-NULL-------+                     |
      |                      '-DEFAULT----'                     |
      '-+---------------------------------------+---fullselect--'
        |       .-,--------------------------.  |
        |       V                            |  |
        '-WITH-----common-table-expression---+--'

Note:

See Queries for the syntax of common-table-expression and fullselect.

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 application server, but it must not identify a catalog table, a summary table, a view of a catalog table, or a read-only view.

A value cannot be inserted into a view column that is derived from:

A constant, expression, or scalar function
The same base table column as some other column of the view
A column derived from a nickname.

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.

(column-name,...)

Specifies the columns for which insert values are provided. Each name must be an unqualified name that identifies a column of the table or view. The same column must not be identified more than once. A view column that cannot accept insert values must not be identified.

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.

The implicit column list is established at prepare time. Hence an INSERT statement embedded in an application program does not use any columns that might have been added to the table or view after prepare time.

VALUES

Introduces one or more rows of values to be inserted.

Each host variable named must be described in the program in accordance with the rules for declaring host variables.

The number of values for each row 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.

expression

An expression can be as defined in Expressions.

NULL

Specifies the null value and should only be specified for nullable columns.

DEFAULT

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 WITH DEFAULT clause is used, the value inserted is as defined for the column (see default-clause in CREATE TABLE).
If the WITH DEFAULT clause, GENERATED clause, and the NOT NULL clause are not used, the value inserted is NULL.
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).

WITH common-table-expression

Defines a common table expression for use with the fullselect that follows. See common-table-expression for an explanation of the common-table-expression.

fullselect

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.

Rules

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 the assignment rules described in "Language Elements".
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 CREATE VIEW.
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.
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 insert values.
Datalinks: Insert statements that include DATALINK values will result in an attempt to link the file if a URL value is included (not empty string or blanks) and the column is defined with FILE LINK CONTROL. Errors in the DATALINK value or in linking the file will cause the insert to fail (SQLSTATE 428D1 or 57050).

Notes

After execution of an INSERT statement that is embedded within a program, the value of the third variable of the SQLERRD(3) portion of the SQLCA indicates the number of rows that were inserted. 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 by:
- 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.
If 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. DB2 assumes 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. See buffered insert in the Application Development Guide for further details.
When a row is inserted into a table that has an identity column, DB2 generates a value for the identity column.
- For a GENERATED ALWAYS identity column, DB2 always generates the value.
- For a GENERATED BY DEFAULT column, if a value is not explicitly specified (with a VALUES clause, or subselect), DB2 generates a value.
The first value generated by DB2 is the value of the START WITH specification for the identity column.
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. ¹⁰²
When inserting into a GENERATED ALWAYS identity column, DB2 will always generate a value 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:
```
   INSERT INTO T2 (EMPID, EMPNAME, EMPADDR)
     VALUES (:hv_valid_emp_id, :hv_name, :hv_addr) 
```
will result in an error.
When inserting into a GENERATED BY DEFAULT column, DB2 will allow an actual value for the column to be specified within the VALUES clause, or from a subselect. However, when a value is specified in the VALUES clause, DB2 does not perform any verification of the value. In order to guarantee uniqueness of the 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. DB2 will generate the value for the identity column.
```
   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.
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:
```
   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).
If 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 above, the intcol1 values from T1 to T2 can be propagated with the following SQL:
```
   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.
When inserting a row into a single column table where the column is defined as a GENERATED ALWAYS identity 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 DB2 generates the value for the identity column.
```
   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)
```
When DB2 generates a value for an identity column, that generated value is consumed; the next time that a value is needed, DB2 will generate a new value. 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. 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.
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 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.

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

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

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);

Footnotes:

¹⁰²: There is no casting of the previous value to the source type prior to the computation.

[ Top of Page | Previous Page | Next Page ]