INSERT (SQL)

Common Set Syntax:

INSERT INTO table-name

(*) [VALUES-clause]
[(column-list)] VALUE-LIST

This document covers the following topics:

For explanations 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