INSERT

The INSERT statement inserts one or more rows into a table using the values provided.

Invocation

This statement can be issued through the DB2eCLP.

Syntax


>>-INSERT INTO--table-name--+-----------------------+----------->
                            |    .-,-----------.    |   
                            |    V             |    |   
                            '-(----column-name-+--)-'   

                .-,--------------.        
                V                |        
>--+-VALUES--(----| expression |-+--)-+------------------------><
   '-SELECT-statement-----------------'   

expression

   .-Operator---------------------.   
   V                              |   
|----+----+--+-literal----------+-+-----------------------------|
     +-+--+  +-special register-+     
     '- --'  +-function---------+     
             '-( expression )---'     

operator

|--+-/--+-------------------------------------------------------|
   +-*--+   
   +-+--+   
   '- --'

Description

INTO table-name: Identifies the table of the insert operation. The name must identify an existing table, but it must not identify a catalog table.
(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. The same column must not be identified more than once.
Omission of the column list is an implicit specification of a list in which every column of the table is identified in left-to-right order.
VALUES: Introduces one row of values to be inserted.
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: The expression can be a literal, special register, function, or a complex expression.
Arithmetic operations on CHAR, VARCHAR, BLOB(n), DATE, TIME and TIMESTAMP data types are not supported.
literal: A literal can be a value of any supported data type INTEGER, SMALLINT, DECIMAL, CHAR(n), VARCHAR(n), BLOB(n), DATE, TIME, or TIMESTAMP.
special register: The special registers CURRENT DATE, CURRENT TIME, and CURRENT TIMESTAMP can be used to produce the current date, time, and timestamp.
SELECT-statement: Specifies a set of new rows in the form of the result table of a select statement. There can be one, more than one, or none. If the result table is empty, SQLCODE is set to +100 and SQLSTATE is set to '02000'. The base object of the select statement cannot be the base object of the INSERT.

Rules

Default values: A default or null value is inserted in any column that is not in the column list. Columns that do not allow default or null values must be included in the column list.
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 be a string column with a length attribute at least as great as the length of the string.
Assignment: Insert values are assigned to columns in accordance with the assignment rules described in the DB2^® Version 9.1 documentation.

Examples

Example 1: Insert an employee with the following specifications into the EMPLOYEE table:

Employee number (EMPNO) is 002001
First name (FIRSTNAME) is John
Last name (LASTNAME) is Harrison
Department number (DEPT) is 600
Phone number (PHONENO) is 4900
Salary (SALARY) is 50000
Date of hire (HIREDATE) is 01/12/1989

INSERT INTO EMPLOYEE 
 VALUES ('002001', 'John', 'Harrison', '600', '4900', 50000, '01/12/1989')

Example 2: Insert a new employee with the following specifications into the EMPLOYEE table:

Employee number (EMPNO) is 003002
First name (FIRSTNAME) is Jim
Last name (LASTNAME) is Gray

     INSERT INTO EMPLOYEE (EMPNO, FIRSTNAME, LASTNAME)
       VALUES ('003002', 'Jim', 'Gray')

Example 3: Create a table EMP_ACT_COUNT. Load EMP_ACT_COUNT with the rows from the EMP_ACT table with an employee number (EMPNO) with the number of projects involved.

CREATE TABLE EMP_ACT_COUNT
   ( EMPNO CHAR(6)  NOT NULL,
     COUNT          INTEGER)

INSERT INTO EMP_ACT_COUNT 
    SELECT EMPNO, COUNT(*)
   FROM EMP_ACT
   GROUP BY EMPNO

Restrictions:

The column data types of SELECT-statement must be identical to the column definition of the target table (except nullability).
ORDER BY and LIMIT clauses are not allowed.
You cannot insert values into an Oracle ROWID column. If you attempt to insert values into this type of column, DB2 Everyplace^® returns SQLSTATE 428C9.