INSERT
Purpose
Use this statement to insert constant values as well as the result of a subquery in a table.
Prerequisites
-
You need either the system privilege
USE ANY SCHEMA
or the object privilegeUSAGE
on the target schema, or the schema must be owned by you or one of your assigned roles. - You need either the system privilege
INSERT ANY TABLE
or the object privilegeINSERT
on the table or its schema, or the table must be owned by you or one of your assigned roles. - To insert the result of a subquery, you need to have the appropriate
SELECT
rights for the objects referenced in the subquery.
Syntax
insert::=
Usage Notes
- The number of target columns of the target table must match the number of constants or the number of
SELECT
columns in a subquery. Otherwise, an exception is thrown. - If only a definite number of target columns are specified for the target table, the entries of the remaining columns will be filled automatically. For identity columns, a monotonically increasing number is
generated, and for columns with default values, their respective default value is used. For all other columns, the value
NULL
is inserted. - If for
INSERT INTO t VALUES
the 'value'DEFAULT
is specified for a column, then the behavior is the same as the implicit insert for unspecified columns. INSERT INTO t DEFAULT VALUES
has the same behavior as if you would specify the literalDEFAULT
for each column.- For information on default values, refer to Default Values section. For information on identity columns, refer to the Identity Columns section.
- For information on the syntax of subqueries, refer to the SELECT statement section under Query language (DQL).
- For information on the
VALUES BETWEEN
syntax, refer to the SELECT statement.