17.05 - Subqueries in INSERT Requests - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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.