INSERT

---

Inserts one or more rows into a table or view.

where columns-values-specification is:

Usage

Embedded/Interactive/ODBC/Procedural/JDBC.

Description

One or more new rows are inserted into the table or view specified in table-name.

If a list of column names is given in columns-values-specification, only the specified columns are assigned values in accordance with the INSERT statement.

The columns not listed are assigned their default value or the NULL value in accordance with the column definition, see CREATE TABLE. If table-name specifies a view, any columns in the base table which are excluded from the view are also assigned their default value or the NULL value in the same way.

If the column name list is omitted, all columns in the table or view are implicitly specified in the order in which they are defined in the table or view. This practice is, however, not recommended when INSERT statements are embedded in application programs, since the semantics of the statement will change if the table or view definition is changed.

Specification of a DEFAULT VALUES clause inserts a single row into the table with the column default value specified for each column in the table.

Values are assigned in order from the items in the VALUES clause or the select-specification to the columns that have been explicitly or implicitly specified. The number of values specified must be the same as the number of columns and the data type of each value must be assignment-compatible with the column into which it is to be inserted.

Specification of a VALUES clause inserts a single row into the table or view. The keyword NULL or DEFAULT can be specified in the VALUES clause to insert the NULL value or the column default value, respectively, into the corresponding column.

Specification of a select-specification instead of a VALUES clause inserts the set of rows resulting from the select-specification into the target table or view. If the set of rows resulting from the select-specification is empty, a NOT FOUND condition code is returned, see Return Status and Conditions.

Language Elements

expression, see Expressions.

select-specification, see The SELECT Expression.

Restrictions

INSERT access is required on the table or view specified in the INTO clause.

If a select-specification is specified, SELECT access is required on the table(s) from which the selection is performed.

In a procedural usage context, the INSERT statement is only permitted if the procedure access-clause is MODIFIES SQL DATA, see CREATE PROCEDURE.

Notes

Expressions used in the VALUES clause cannot refer to column names or set functions.

If the row or rows inserted do not conform to constraints imposed on the table, no rows are inserted. Constraints are as follows:

• Values in the primary key and unique keys of the base table may not be duplicated. This also applies to unique secondary indexes.
• FOREIGN KEY constraints must be observed.
• CHECK constraints in table, column and domain definitions must be observed for insertions.
• For insertion into views defined WITH CHECK OPTION, inserted values must conform to the view definition.

Example

 INSERT INTO countries (country_code, country, currency_code) VALUES
     ('CX', 'Christmas Island', 'AUD');

Standard Compliance

Standard	Compliance	Comments
SQL-2011	Core	Fully compliant.
SQL-2011	Features outside core	Feature F222, "INSERT statement: Default values clause." Feature F781, "Self referencing operations" the table in the insert clause may occur in the query specification.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se