INSERT Statement (insert_statement)

The INSERT statement (insert_statement) creates new rows in a table.

Structure

Syntax Syntax

<insert_statement> ::=
  INSERT [INTO] <table_name>
    [(<column_name>,...)]
    VALUES (<insert_expression>,...)
    [<duplicates_clause>]
    [IGNORE TRIGGER] 
    [NOWAIT]
| INSERT [INTO] <table_name>
    [(<column_name>,...)]
    <query_expression>
    [<duplicates_clause>]
    [IGNORE TRIGGER]
    [NOWAIT]
| INSERT [INTO] <table_name>
    SET <set_insert_clause>,...
    [<duplicates_clause>]
    [IGNORE TRIGGER]
    [NOWAIT]
<!  This SQL clause is no longer recommended to be used
    and might be removed from future versions  !>

<insert_expression> ::=
  <extended_expression>
| <subquery>

<extended_expression> ::=
  <expression>
| DEFAULT
| STAMP

<set_insert_clause> ::=
  <column_name> = <extended_value_spec>

End of the code.

Examples

SQL Tutorial, Inserting, Updating and Deleting Rows

Explanation

The table name must identify an existing base table, view table or a synonym.

The database user must have the INSERT privilege for the table identified by the table name. If the table name identifies a view table, it may be the case that the owner of the view table has not been granted the INSERT privilege as the view table is not updatable.

Column Names

If column names are specified (in syntax clauses column_name or set_insert_clause), all column names must be columns in the specified table. If the table was defined without a key (the SYSKEY column has been created internally by the database), the SYSKEY column cannot appear in the column names sequence or in a SET INSERT clause. A column cannot occur more than once in a sequence of column names or in more than one SET INSERT clause.

A specified column (identified by column_name or the column name in the set_insert_clause) is a target column. Target columns can be specified in any order.

The number of selected columns specified in the QUERY expression (query_expression) must be the same as the number of target columns.
All mandatory columns of the table identified by the table name must be target columns.
If the table name identifies a view table, rows are inserted into the underlying base table(s) of the view table. In such cases, the target columns of the table name specified correspond to the columns of the underlying base tables in the view table. The term “target column” always refers to the corresponding column in the base table in the following description.

If you specify neither a column name nor a SET INSERT clause, the effect is the same as if you specify a sequence of columns containing all table columns in the sequence in the CREATE TABLE statement or CREATE VIEW statement. In such cases, all table columns defined by the user are target columns.

SET <set_insert_clause>

Caution Caution

This SQL clause is no longer recommended to be used and might be removed from future versions.

End of the caution.

<insert_expression>

The number of insert_expression expressions must be equal to the number of target columns. The ith expression is assigned to the ith column name.
You can specify both an extended_expression and subquery at the same time.
You can also specify one or more subqueries.
The subqueries you specify may return no more than one results row.

<extended_expression>

You specify an extended_expression using an expression or one of the DEFAULT or STAMP keywords.

Expression (expression)
An expression in an INSERT statement may not contain a column specification. In an expression, the value specified by a parameter specification is the value of the parameter identified by the specification. If you specify an indicator parameter and this has a negative value, the value defined by the parameter specification is a NULL value.
DEFAULT keyword
DEFAULT denotes the value used as the DEFAULT for the column.
STAMP keyword
The database system is capable of generating unique values. These comprise a series of consecutive numbers starting at X'000000000001'. The values are assigned in ascending order. There is no guarantee that a sequence of values is uninterrupted. The STAMP keyword returns the next value generated by the database system. The STAMP keyword can be used in the INSERT statement or in the UPDATE statement, but only for columns of the data type CHAR(n) BYTE with n>=8.

If the user wants to find out the generated value before it is applied to a column, the NEXT STAMP SQL statement must be used.

IGNORE TRIGGER

If you specify IGNORE TRIGGER, the INSERT statement does not execute an INSERT trigger. IGNORE TRIGGER must be specified within a CREATE TRIGGER statement only.

NOWAIT

If you specify NOWAIT, the system does not wait for the lock to be released where a lock collision occurs. Instead, it returns an error message immediately.

More Information

Data Type of the Target Column and Data Type of the Value to be Inserted

Join View Table, QUERY Expression, CONSTRAINT Definition, Trigger in INSERT Statement

Data Manipulation