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 privilege USAGE 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 privilege INSERT 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.

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 literal DEFAULT 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.

Examples

INSERT INTO t VALUES (1, 2.34, 'abc');

INSERT INTO t VALUES (2, 1.56, 'ghi'), (3, 5.92, 'pqr');

INSERT INTO t VALUES (4, DEFAULT, 'xyz');

INSERT INTO t (i,k) SELECT * FROM u;

INSERT INTO t (i) SELECT max(j) FROM u;

INSERT INTO t DEFAULT VALUES;

INSERT INTO t VALUES BETWEEN 1 AND 100;
INSERT INTO t (i) VALUES BETWEEN 1 AND 100 WITH STEP 4;