INSERT/INSERT ... SELECT Syntax | SQL Statements | Teradata Vantage - INSERT/INSERT ... SELECT Syntax - Advanced SQL Engine - Teradata Database

{ INSERT | INS } [ with_isolated_loading ] [ INTO ] table_name
  { [ VALUES ] ( expression [,...] ) |
    ( column_name [,...] ) VALUES ( expression [,...] ) |
    [ ( column_name [,...] ) ]
    subquery
    [ hash_by ]
    [ local_order_by ]
    [ logging_errors ] |
    JSON { 'JSON_string' | ? } |
    DEFAULT VALUES
  } [;]

Syntax Elements

with_isolated_loading

WITH [NO] [CONCURRENT] ISOLATED LOADING

Without NO, the INSERT can be performed as a concurrent load isolated operation.

With NO, the INSERT is not performed as a concurrent load isolated operation.

CONCURRENT can be included for readability.

INTO

Keyword that is required for ANSI compliance. If you do not specify INTO, then the request is non-ANSI standard.

table_name

Name of the table directly or by means of a view.

VALUES

Keyword that is required for ANSI compliance. If you do not specify INTO, then the request is non-ANSI standard.

The colon is optional with host variables used in this clause.

column_name	VALUES Keyword
Specified.	Required.
Not specified.	Optional.

expression

constant or constant expression to be inserted into the new row for the columns specified in column_name. A constant expression is an expression containing only constants (for example, 3+5, or 19000/12).

If you are inserting into a UDT column, expression includes the appropriate NEW expression with the necessary constructor and mutator methods specified using dot notation. For more information, see Teradata Vantage™ - SQL Operators and User-Defined Functions, B035-1210.

The system values CURRENT_DATE, DATE, CURRENT_TIME, TIME, and USER can be included in a constant expression. You can also use the DEFAULT function to insert the default value for a column.

You can insert a NULL by specifying the reserved word NULL as an expression value.

Values obtained from imported data, with a USING modifier, or as macro parameters, are accepted as constants.

When a column name list is not specified, then values are inserted in column definition order, from left to right. Use commas to indicate skipped columns. See Example: Insert Ordered Entries.

The derived period column position is skipped when mapping the values to the columns.

When a column name list and an expression list are used, values are assigned by matching column_name position with expression position. The two lists must have the same number of items.

When an element of the expression list is omitted, it is treated as a NULL. This is not valid in ANSI SQL.

You cannot specify a value for a derived period column.

column_name

Name of a column for which the insert operation supplies a new row value.

Column names can be specified in any order.

If a column_name is omitted, any default value defined in the CREATE TABLE or CREATE VIEW request is used.

You cannot specify a derived period column name.

The begin and end columns of a derived period column can be independently updated. The end column value must be greater than the begin column value of the derived period column.

subquery

The row or rows to be inserted consist of column values accessed by a query specification.

If a column name list is not specified, then the SELECT request must query the same number of columns as there are in the table that is receiving new rows.

You can specify nulls in the select expression list for columns that are not to be assigned values.

You cannot specify an ORDER BY clause as part of a subquery in an INSERT ... SELECT request.

If the INSERT operation includes a column name list, values are assigned by matching column_name position with position of items in the select expression list.

hash_by

HASH BY hash_list | RANDOM }

local_order_by

LOCAL ORDER BY local_order_by_spec [,...]

logging_errors

LOGGING [ ALL ] ERRORS [ WITH { NO LIMIT | LIMIT OF error_limit } ]

If you do not specify the LOGGING ERRORS option, the system does not provide error handling. If an error occurs, the following session-mode behavior occurs:

• If the current session mode is ANSI, the erring request aborts and rolls back.
• If the current session mode is Teradata, the erring transaction aborts and rolls back.

In certain cases, such as a deadlock, the erring transaction roles back.

ALL: The optional keyword ALL is the default. If you log errors, then you must log all errors.

WITH NO LIMIT: There is no limit to the number of errors that can be logged in the error table associated with the target data table for this INSERT ... SELECT load operation. Errors are logged up to the system-determined limit of 16,000,000 errors.

WITH LIMIT OF error_limit: The limit on the number of errors that can be logged in the error table associated with the target data table for this INSERT ... SELECT load operation is error_limit.

The value you specify for error_limit can be from 1 through 16,000,000. If you do not specify a value for the LIMIT option, the system defaults to a 10 error limit.

JSON_string

Specifies a literal string of data in JSON format.

Specifies JSON format input. See Rules for Using the JSON Option.

For information about formatting JSON data, see Teradata Vantage™ - JSON Data Type, B035-1150.

?

Specifies parameterized SQL.

For parameterized SQL, INSERT JSON supports VARCHAR, CLOB, and external JSON data types. However, the operation is a two-AMP process. For optimal performance, specify a JSON literal.

DEFAULT VALUES

A row consisting of default values is to be added to table_name.

If a DEFAULT phrase is not defined for the column, the system assumes a default of null for it unless it is defined as NOT NULL, in which case an error is returned and the insertion fails.

Embedded SQL does not support this option for INSERT.

hash_list

{ column_name | column_position | column_expression } [,...]

Hash rows being inserted into a NoPI table across the AMPs.

RANDOM

Hash rows being inserted into a NoPI table across the AMPs randomly, a block at a time.

local_order_by_spec

{ column_name | column_position | column_expression }
  [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

Order rows being inserted into a table locally on the AMPs.

error_limit