An INSERT operation that uses a subquery, referred to as an INSERT … SELECT request, differs from a simple INSERT in that many rows can be inserted in a single operation, and the row values can come from more than one table.
The query specification must always include a FROM table_name clause.
Also, an INSERT request that includes a subquery must define a column name list when the following conditions exist:
- The number of columns listed in the query specification differs from the number of columns in the table receiving new rows.
- The order of columns listed in the query specification differs from the order the columns were defined in the table receiving new rows.
Using INSERT … SELECT With Subqueries
You cannot specify an ORDER BY clause in the SELECT component of an INSERT … SELECT request when the SELECT is a subquery.
Using Scalar Subqueries in INSERT Requests
The following rules and restrictions apply to specifying scalar subqueries in INSERT and INSERT … SELECT requests:
- You can specify a scalar subquery as a parameterized value in the value list of a simple INSERT request, but the database always interprets it as a noncorrelated scalar subquery. See Example: INSERT Using a Scalar Subquery.
- You cannot specify a noncorrelated scalar subquery as a value in a value list that is assigned to an identity column in a simple INSERT request.
- You can specify an INSERT request with scalar subqueries in the body of a trigger.
However, you cannot specify a simple INSERT request with a scalar subquery in its value list in the body of a row trigger.
- You can specify a scalar subquery in the SELECT component of an INSERT … SELECT request.
- Vantage processes any noncorrelated scalar subquery specified in the SELECT component of an INSERT … SELECT in a row trigger as a single-column single-row spool instead of as a parameterized value.