DB2 10.5 for Linux, UNIX, and Windows

INSERT statement

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.

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

The privileges held by the authorization ID of the statement must include at least one of the following authorities:

INSERT privilege on the target table, view, or nickname
CONTROL privilege on the target table, view, or nickname
DATAACCESS authority

In addition, for each table, view, or nickname 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 authorities:

SELECT privilege
CONTROL privilege
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.

Syntax


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

Description

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

The object must not be a catalog table, a system-maintained materialized query table, a view of a catalog table, or a read-only view, unless an INSTEAD OF trigger is defined for the insert operation on the subject view. Rows inserted into a nickname are placed in the data source object to which the nickname refers.

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

(column-name,...)

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.

include-columns

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.

INCLUDE: 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.
column-name: 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).
data-type: Specifies the data type of the include column. The data type must be one that is supported by the CREATE TABLE statement.

VALUES

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.

expression

An expression can be any expression defined in the "Expressions" topic. 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

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 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 default-clause in "CREATE TABLE").
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.

row-expression

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.

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.

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.

WITH

Specifies the isolation level at which the fullselect is executed.

RR: Repeatable Read
RS: Read Stability
CS: Cursor Stability
UR: Uncommitted Read

The default isolation level of the statement is the isolation level of the package in which the statement is bound. The WITH clause has no effect on nicknames, which always use the default isolation level of the statement.

Rules

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 "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.
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).
Extended 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).

Notes

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 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. 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.
When 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.
The first value generated 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. (There is no casting of the previous value to the source type before the computation.)
When 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:
```
   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 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.
When 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.
```
   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 the database manager.
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 previously, 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 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.
```
   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 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.
Inserting 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.
Inserting 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.

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);
```
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 VALUES clause.
```
   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.