Inserts one or more rows into a table or view.
One or more new rows are inserted into the table or view specified in
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-namespecifies 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
INSERTstatements 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 VALUESclause 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
VALUESclause 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
VALUESclause inserts a single row into the table or view. The keyword
DEFAULTcan be specified in the
VALUESclause to insert the null value or the column default value, respectively, into the corresponding column.
Specification of a select-specification instead of a
VALUESclause 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 FOUNDcondition code is returned, see Return Status and Conditions.
expression, see Expressions.
select-specification, see The SELECT Expression.
with-clause, see The WITH Clause.
INSERTaccess is required on the table or view specified in the
If a select-specification is specified,
SELECTaccess is required on the table(s) from which the selection is performed.
In a procedural usage context, the
INSERTstatement is only permitted if the procedure
MODIFIES SQL DATA, see CREATE PROCEDURE.
Expressions used in the
VALUESclause 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 KEYconstraints must be observed.
CHECKconstraints 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.
ExampleINSERT INTO countries (country_code, country, currency_code) VALUES ('CX', 'Christmas Island', 'AUD');
Mimer Information Technology AB
Phone: +46 18 780 92 00