An INSERT operation that uses a subquery, called 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 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 getting new rows.
- The order of columns listed in the query specification differs from the order the columns were defined in the table getting 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 interprets the parameterized value 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.