INSERT (SQL)

#### Search Results

Search

# INSERT (SQL)

Common Set Syntax:

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

Extended Set Syntax:

This documentcovers the following topics:

- Function

- Syntax Description

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

Belongs to Function Group: Database Access and Update

## 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.
`VALUES-clause` | Values Clause: With the `VALUES` clause, you insert a single row into the table. See VALUES Clause below.

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

`VALUES` ( |  | `VIEW` view-name insert-item-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) |  |

Extended Set Syntax:

| `VALUES` |  | (`VIEW` view-name) (insert-item-list) |  |
select-expression |  | `WITH` |  | `RR` `RS` `CS` |  |

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)
    ...
`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. It is only valid against Db2 databases. When used against other databases, it will cause runtime errors.
`CS` | Cursor Stability
`RR` | Repeatable Read
`RS` | Read Stability

### On this page

- Function
- Syntax Description