INSERT Statement (insert_statement) 

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

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]

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

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

Examples

SQL Tutorial, Inserting, Changing and Deleting Rows

Explanation

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

The 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 updateable.

<column_name>, <set_insert_clause>

If column names column_name or a SET INSERT clause set_insert_clause are specified, 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.

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

●      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.

●      The number of insert_expression expressions must be equal to the number of target columns. The i^th expression is assigned to the i^th 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.

●      The number of selected columns specified in the  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 view table’s underlying base table(s). In such cases, the target columns of the table name specified correspond to the columns of the view table’s underlying base tables. The term “target column” always refers to the corresponding column in the base table in the following.

<extended_expression>

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

●       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 defi ned by the parameter specification is a NULL value.

●      DEFAULT keyword
DEFAULT denotes the value used as the DEFAULT for the column.

●      STAMP key word
The database system is capable of generating unique values. These comprise a series of consecutive numbers starting at X'000000000001'. Values are assigned in ascending order. There is no guarantee that the sequence of values will be uninterrupted. The STAMP key word returns the next value generated by the database system.
It can be used in the INSERT statement or the UPDATE statement, though only on columns of data type CHAR(n) BYTE where n>=8.
If the user wants to find out the generated value before it is applied to the column, the SQL statement NEXT STAMP statement must be used.

IGNORE TRIGGER

You use IGNORE TRIGGER to ensure that no INSERT trigger is executed by the INSERT statement.

NOWAIT

If you specify NOWAIT, the system does not wait for the lock to be released where a lock collision occurs, but 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