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

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 value that is inserted depends on how the column was defined, as follows:

• If the WITH DEFAULT clause is used, the default inserted is as defined for the column (see default-clause in ALTER TABLE).

• If the WITH DEFAULT clause or the NOT NULL clause is not used, the value inserted is NULL.

• If the NOT NULL clause is used and 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.

• 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 Chapter 3, "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.

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

[ DB2 List of Books | Search the DB2 Books ]