Values Options - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

Values Options

 

Syntax Element …

Specifies …

VALUES

a keyword required for ANSI compliance.

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

  • When column_name is specified, the VALUES keyword is required.
  • When column_name is not specified, the VALUES keyword is 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” on page 378.

    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

    the 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

    that 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.