- 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 current server, but it must not identify
a catalog table, a view of a catalog table, or a view that is not insertable.
For an explanation of insertable views, see CREATE VIEW.
- (column-name,...)
- Specifies the columns for which insert values are provided. Each name
must be a name that identifies a column of the table or view. The same column
must not be identified more than once. A view column that is not updatable
must not be identified. 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 those columns. For an explanation of updatable columns in views,
see CREATE VIEW.
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.
If the INSERT statement is embedded in an application and
the referenced table or view exists at create program time, the statement
is prepared at create program time. Otherwise, the statement is prepared at
the first successful execute of the INSERT statement.
- OVERRIDING SYSTEM VALUE or OVERRIDING USER VALUE
- Specifies whether system generated values or user-specified values for
a ROWID or identity column are used. If OVERRIDING SYSTEM VALUE is specified,
the implicit or explicit list of columns for the INSERT statement must contain
a column defined as GENERATED ALWAYS. If OVERRIDING USER VALUE is specified,
the implicit or explicit list of columns for the INSERT statement must contain
a column defined as either GENERATED ALWAYS or GENERATED BY DEFAULT.
- OVERRIDING SYSTEM VALUE
- Specifies that the value specified in the VALUES clause or produced
by a fullselect for a column that is defined as GENERATED ALWAYS is used.
A system-generated value is not inserted.
- OVERRIDING USER VALUE
- Specifies that the value specified in the VALUES clause or produced
by a fullselect for a column that is defined as either GENERATED ALWAYS or
GENERATED BY DEFAULT is ignored. Instead, a system-generated value is inserted,
overriding the user-specified value.
If neither OVERRIDING SYSTEM VALUE nor OVERRIDING USER VALUE is
specified:
- VALUES
- Specifies one or more new rows to be inserted.
Each variable in the
clause must identify a host structure or variable that is declared in accordance
with the rules for declaring host structures and variables. In the operational
form of the statement, a reference to a host structure is replaced by a reference
to each of its variables. For further information on variables and structures,
see References to host variables and Host structures.
The number of
values for each row in the VALUES clause 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 of the type described in Expressions, that does not include an aggregate function or column name.
If expression is a variable, the variable can
identify a structure.
- DEFAULT
- Specifies that the default value is assigned to a column. 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 column-definition in CREATE 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.
- If the column is a ROWID or identity column, the database manager will generate a new
value.
DEFAULT must be specified for a ROWID or an identity column
that was defined as GENERATED ALWAYS unless OVERRIDING USER VALUE is specified
to indicate that any user-specified value will be ignored and a unique system-generated
value will be inserted.
- NULL
- Specifies the value for a column is the null value. NULL should only
be specified for nullable columns.
- select-statement
- Specifies a set of new rows in the form of the result table of a select-statement. The FOR READ ONLY, FOR UPDATE, and OPTIMIZE clauses
are not valid for a select-statement used with insert. If an ORDER
BY clause is specified on the select-statement, the rows are inserted
according to the values of the columns identified in the ORDER BY clause.
For an explanation of select-statement, see select-statement.
There can be one, more than one, or zero rows inserted when
using the select-statement. If no rows are inserted, SQLCODE is set
to +100 and SQLSTATE is set to '02000'.
When the base object
of the INSERT and a base object of any subselect in the select-statement are the same table, the select statement is completely evaluated before
any rows are inserted.
The number of columns in the result
table must equal the number of names implicitly or explicitly specified in
the column-name 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.
- isolation-clause
- Specifies the isolation level to be used for this statement.
- WITH
-
Introduces the isolation level, which may be one of:
-
RR Repeatable read
-
RS Read stability
-
CS Cursor stability
-
UR Uncommitted read
-
NC No commit
If isolation-clause is not specified the default isolation
is used. See isolation-clause for a description of how the default is
determined.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.