Subqueries in INSERT Requests - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2024-12-13
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantageā„¢

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.