INSERT...SELECT

The INSERT... SELECT statement inserts the results of a query into a table.

Required privilege

No privilege is required for the object owner.

INSERT and SELECT for another user's object.

SQL syntax

INSERT INTO [Owner.]TableName [(ColumnName [,...])] InsertQuery

Parameters

The INSERT... SELECT statement has the parameters:

Parameter	Description
`[Owner.]TableName`	Table to which data is to be added.
`ColumnName`	Column for which values are supplied. If you omit any of the table's columns from the column name list, the INSERT...SELECT statement places the default value in the omitted columns. If the table definition specifies NOT NULL, without a default value, for any of the omitted columns, the INSERT...SELECT statement fails. You can omit the column name list if you provide values for all columns of the table in the same order the columns were specified in the CREATE TABLE statement. If too few values are provided, the remaining columns are assigned default values.
`InsertQuery`	Any supported SELECT query. See "SELECT".

Description

The column types of the result set must be compatible with the column types of the target table.
You can specify a sequence CURRVAL or NEXTVAL when inserting values.
The target table cannot be referenced in the FROM clause of the InsertQuery.
In the InsertQuery, the ORDER BY clause is allowed. The sort order may be modified using the ORDER BY clause when the result set is inserted into the target table, but the order is not guaranteed.
The INSERT operation fails if there is an error in the InsertQuery.
A RETURNING clause cannot be used in an INSERT... SELECT statement.

Examples

New rows are added to the purchasing.parts table that describe which parts are delivered in 20 days or less.

INSERT INTO purchasing.parts
SELECT partnumber, deliverydays
FROM purchasing.supplyprice
WHERE deliverydays < 20;