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.

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL syntax

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

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". You can specify a statement level optimizer hint after the SELECT verb. See "Statement Level Optimizer Hints" for more information on statement level optimizer hints.

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. See "Using CURRVAL and NEXTVAL in TimesTen Classic" for more details.

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

  • The SELECT subquery in a UNION, UNION ALL, MINUS, or INTERSECT must have the same number of projected expressions.

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;