INSERT (SQL)

#### Search Results

Search

# INSERT (SQL)

Common Set Syntax:

`INSERT` `INTO` table-name |  | (`*`) [VALUES-clause] [(column-list)] VALUE-LIST |

Extended Set Syntax:

`INSERT` `INTO` table-name |  | (`*`) [`OVERRIDING USER VALUE`] [VALUES-clause] [(column-list)] [include-columns] [`OVERRIDING USER VALUE`] VALUE-LIST |

This documentcovers the following topics:

- Function

- Syntax Description

- Example

For an explanation of the symbols used in the syntax diagram, see Syntax Symbols.

Belongs to Function Group: Database Access and Update

See also INSERT - SQL in the Natural for Db2 part of the Database Management System Interfaces documentation:

## Function

The SQL `INSERT` statement is used to add one or more new rows to a table.

## Syntax Description

Syntax Element | Description
--- | ---
`INTO table-name` | INTO Clause: In the `INTO` clause, the table is specified into which the new rows are to be inserted. See further information on `table-name`.
`column-list` | Column List: Syntax:
    column-name...
In the `column-list`, one or more `column-names` can be specified, which are to be supplied with values in the row currently inserted. If a `column-list` is specified, the sequence of the columns must match with the sequence of the values either specified in the `insert-item-list` or contained in the specified view (see below). If the `column-list` is omitted, the values in the `insert-item-list` or in the specified view are inserted according to an implicit list of all the columns in the order they exist in the table.
`include-columns` | Include Columns Clause: Specifies a set of columns that are included, along with the columns of `table-name`, in the result table of the `INSERT` statement when it is nested in the `FROM` clause of a `SELECT` statement. For further details, see include-columns.
`VALUES-clause` | Values Clause: With the `VALUES` clause, you insert a single row into the table. See VALUES Clause below.
`OVERRIDING USER VALUE` | OVERRIDING USER VALUE Clause: This clause belongs to the SQL Extended Set. When you specify this clause, the value specified in the `VALUES` clause or produced by a fullselect for a column that is defined as `GENERATED ALWAYS` will be ignored.

#### VALUES Clause

With the `VALUES` clause, you insert a single row into the table. Depending on whether an asterisk (*) or a `column-list` has been specified, the `VALUES` clause can take one of the following forms:

#### VALUES Clause with Preceding Asterisk Notation

`VALUES` (`VIEW` view-name)

If asterisk notation is specified, a view must be specified in the `VALUES` clause. With the field values of this view, a new row is inserted into the specified table using the field names of the view as column names of the row.

#### VALUES Clause with Preceding Column List

[(column-list)] [`OVERRIDING USER VALUE`] VALUE-LIST

If a `column-list` is specified and a view is referenced in the `VALUES` clause, the number of items specified in the column list must correspond to the number of fields defined in the view within the VALUE-LIST.

If no `column-list` is specified, the fields defined in the view are inserted according to an implicit list of all the columns in the order they exist in the specified table.

#### VALUE-LIST

Common Set Syntax:

| `VALUES` |  | (`VIEW` view-name) (insert-item-list) |  | [FOR-n-ROWS-clause] |

Extended Set Syntax:

| `VALUES` |  | (`VIEW` view-name) (insert-item-list) |  | [FOR-n-ROWS-clause] |
[`WITH_CTE` common-table-expression,...] select-expression |  | `WITH` |  | `RR` `RS` `CS` |  |  | [`QUERYNO` integer] |

Syntax Description:

Syntax Element | Description
--- | ---
`VIEW view-name` | View Name: With the field values of this view, a new row is inserted into the specified table using the field names of the view as column names of the row.
`insert-item-list` | INSERT Single Row: In the `insert-item-list`, you can specify one or more values to be assigned to the columns specified in the `column-list`. The sequence of the specified values must match the sequence of the columns. If no `column-list` is specified, the values in the `insert-item-list` are inserted according to an implicit list of all the columns in the order they exist in the table. The values to be specified in the `insert-item-list` can be `constants`, `parameters`, `special-registers` or `NULL`. See the section Basic Syntactical Items for information on `view-name`, `constant` and `parameter`. See also the information on `special-register`. If the value `NULL` has been assigned, this means that the addressed field is to receive no value (not even the value `0` or "blank"). Example - INSERT Single Row:
    ...
    INSERT INTO SQL-PERSONNEL (NAME,AGE)
      VALUES ('ADKINSON',35)
    ...
`FOR-n-ROWS-clause` | FOR n Rows Clause: Optional clause, see FOR-n-ROWS-Clause below.
`WITH_CTE common-table-expression` | WITH_CTE Clause: This clause belongs to the SQL Extended Set. This optional clause permits defining a result table which can be referenced in any `FROM` clause of the `SELECT` statement that follows. Multiple common-table-expressions can be specified following the single `WITH_CTE` keyword. Each common-table-expression can also be referenced in the `FROM` clause of subsequent common-table-expression. For more information, see WITH_CTE common-table-expression in the section SELECT - SQL.
`select-expression` | INSERT Multiple Rows: This clause belongs to the SQL Extended Set. With a select-expression, you insert multiple rows into a table. The select-expression is evaluated and each row of the result table is treated as if the values in this row were specified as values in a VALUES Clause of a single-row `INSERT` operation. For further information, see Select Expressions. Example - Insert Multiple Rows:
    ...
    INSERT INTO SQL-RETIREE (NAME,AGE,SEX)
      SELECT LASTNAME, AGE, SEX
      FROM SQL-EMPLOYEES
      WHERE AGE > 60
    ...
Note The number of rows that have actually been inserted can be ascertained by using the system variable `*ROWCOUNT`.
`WITH RR/RS/CS` | WITH Isolation Level Clause: This clause belongs to the SQL Extended Set. This clause allows the explicit specification of the isolation level used when locating the rows to be inserted.
`CS` | Cursor Stability
`RR` | Repeatable Read
`RS` | Read Stability
`QUERYNO_integer` | QUERYNO Clause: This clause belongs to the SQL Extended Set. This clause explicitly specifies the number to be used in `EXPLAIN` output and trace records for this statement.

#### FOR-n-ROWS-Clause

`FOR` |  | [:]_host-variable integer |  | `ROWS` | [atomic-clause]

This clause is composed of the following subclauses:

#### FOR [:] hostvariable/integer ROWS Clause

`FOR` |  | [:]_host-variable integer |  | `ROWS`

The specification of this clause is optional. It should only be specified, if

- compiler option `DB2ARRY` is specified and

- multiple rows are to be inserted from arrays specified in the insert-item-list of the VALUES Clause.

If specified, [:]_hostvariable/integer determines the number of rows to be inserted into the Db2 table from the arrays specified in the insert-item-list of the VALUES Clause starting with the first occurrence.

The purpose of this clause is to improve the performance of programs inserting rows from Natural arrays in a loop. By using this clause, the rows contained in the arrays can be inserted by one SQL statement.

See example below.

See also the Natural for Db2 part of the Database Management System Interfaces documentation.

#### ATOMIC Clause

| `ATOMIC` `NOT ATOMIC CONTINUE ON SQLEXCEPTION` |

This clause specifies whether the insertion of multiple rows should be treated by DB2 as an atomic operation or not.

It should only be specified, if

- compiler option `DB2ARRY` is specified and

- multiple rows are to be inserted from arrays specified in the `insert-item-list` of the VALUES Clause.

Syntax Description:

Syntax Element | Description
--- | ---
`ATOMIC` | Specifies that in case of any error no row is inserted into the target table. This is the default value.
`NOT ATOMIC CONTINUE ON SQLEXCEPTION` | Specifies that in case of errors all rows for which no error occurred are inserted while those rows for which errors occurred are discarded by Db2.

See the Db2 SQL REFERENCE for SQLCODEs returned in such cases.

## Example

    DEFINE DATA LOCAL
    01 NAME        (A20/1:10)  INIT <'ZILLER1','ZILLER2','ZILLER3','ZILLER4'
                                    ,'ZILLER5','ZILLER6','ZILLER7','ZILLER8'
                                    ,'ZILLER9','ZILLERA'>
    01 ADDRESS     (A100/1:10) INIT <'ANGEL STREET 1','ANGEL STREET 2'
                                    ,'ANGEL STREET 3','ANGEL STREET 4'
                                    ,'ANGEL STREET 5','ANGEL STREET 6'
                                    ,'ANGEL STREET 7','ANGEL STREET 8'
                                    ,'ANGEL STREET 9','ANGEL STREET 10'>
    01 DATENATD (D/1:10)  INIT <D'1954-03-27',D'1954-03-27',D'1954-03-27'
                                ,D'1954-03-27',D'1954-03-27',D'1954-03-27'
                                ,D'1954-03-27',D'1954-03-27',D'1954-03-27'
                                ,D'1954-03-27'>
    01 SALARY      (P4.2/1:10) INIT <1000,2000,3000,4000,5000
                                    ,6000,7000,8000,9000,9999>
    01 L§ADDRESS   (I2/1:10) INIT <14,14,14,14,14,14,14,14,14,15>
    01 N§ADDRESS   (I2/1:10) INIT <00,00,00,00,00,00,00,00,00,00>
    01 ROWS        (I4)
    01 INDEX       (I4)
    01 V1 VIEW OF NAT-DEMO_ID
    02 NAME
    02 ADDRESS     (EM=X(20))
    02 DATEOFBIRTH
    02 SALARY
    01 ROWCOUNT  (I4)
    END-DEFINE
    OPTIONS DB2ARRY=ON                  /* <-- ENABLE DB2 ARRAY
    ROWCOUNT := 10
    INSERT INTO NAT-DEMO_ID
           (NAME,ADDRESS,DATEOFBIRTH,SALARY)
           VALUES
           (:NAME(*),                   /* <-- ARRAY
            :ADDRESS(*)                 /* <-- ARRAY
            INDICATOR :N§ADDRESS(*)     /* <-- ARRAY
            LINDICATOR :L§ADDRESS(*),   /* <-- ARRAY DB2 VCHAR
            :DATENATD(1:10),            /* <-- ARRAY NATURAL DATES
            :SALARY(01:10)              /* <-- ARRAY NATURAL PACKED
           )
           FOR :ROWCOUNT ROWS
    SELECT * INTO VIEW V1 FROM NAT-DEMO_ID WHERE NAME > 'Z'
    DISPLAY V1                          /* <-- VERIFY INSERT
    END-SELECT
    END

### On this page

- Function
- Syntax Description
- Example