INSERT

Purpose

Use this statement to insert constant values as well as the result of a subquery in a table.

Prerequisites

• You need to have the system privilege INSERT ANY TABLE, object privilege INSERT on the table or its schema, or the table belongs to the current user or one of the user roles.
• To insert the result of a subquery, you need to have the appropriate SELECT rights for the objects referenced in the subquery.

Syntax

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

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;