Syntax Elements - Teradata Database

SQL Data Manipulation Language
Product
Teradata Database
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-04-25
dita:mapPath
psg1480972718197.ditamap
dita:ditavalPath
changebar_rev_16_10_exclude_audience_ie.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

Isolated Loading Options

WITH ISOLATED LOADING
The INSERT can be performed as a concurrent load isolated operation on a table that is defined with load isolation.
NO
The INSERT is not performed as a concurrent load isolated operation.
CONCURRENT
Optional keyword that you can include for readability.

Table Name

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 Options

VALUES
Keyword required for ANSI compliance.
The colon is optional with host variables used in this clause.
column_name VALUES keyword
Specified. Required.
Not specified. Optional.
expression
A 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 details, see SQL Functions, Operators, Expressions, and Predicates .
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_name can be specified in any order.
If a column_name is omitted, then 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 the 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.
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.

Subquery Options

column_name
Name of a column for which the insert operation supplies a new row value.
column_name can be specified in any order.
If a column_name is omitted, then 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 the 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_list
Hash rows being inserted into a NoPI table across the AMPs based on the hash_list.
HASH BY RANDOM
Hash rows being inserted into a NoPI table across the AMPs randomly, a block at a time.
LOCAL ORDER BY order_list
Order rows being inserted into a table locally on the AMPs.

Error Logging Options

Following are the error logging options you can specify for tables that have an associated error table. See “CREATE ERROR TABLE” in SQL Data Definition Language .

LOGGING ERRORS
LOGGING ALL ERRORS
Log all data errors, reference index errors, and USI errors.
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.
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.