{ 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
-