Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 158 159 160 161 162 163 < 164 > 165 166 167 168 169 170 .. 241 >> Next

HELP ‘multiload’; a list of MultiLoad commands.
HELP ‘sql update’; the syntax for the UPDATE
command.

Teradata RDBMS for UNIX SQL Reference

8-191
Teradata SQL Syntax Guide

INSERT

Function

Syntax

INSERT

The INSERT statement adds new rows to a table by directly specifying the row data to be inserted, or by retrieving the new row data from another table.

The INS abbreviation is non-ANSI and is flagged when the SQL Flagger is enabled.

INSERT-INS----

I-INTO-I

tname

where:

Syntax Element ... Specifies . . .
INTO a keyword that is required for ANSI compliance. If not used, it is flagged when the SQL flagger is enabled.
tname the name of the table directly or by means of a view.
VALUES a keyword that is required for ANSI compliance. When col_name is omitted, the VALUES keyword is optional. It is required when a col_name list is specified.
col_name the name of a column for which the insert operation supplies a new row value. col_name may be specified in any order. If a col_name is omitted, any default value defined in the CREATE TABLE or CREATE VIEW statement is used.

8-192

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

INSERT

Syntax Element ... Specifies . . .
expr a constant or constant expression to be inserted into the new row for the columns specified in cname. A constant expression is an expression containing only constants (for example, 3+5, or 19000/12). The system values DATE, TIME, and USER may be included in a constant expression. A NULL value may be inserted by specifying the reserved word NULL as an expr value. Values obtained from imported data, with a USING modifier, or as macro parameters, are accepted as constants. If a column name list and an expr list are used, values are assigned by matching cname position with expr position. The two lists must have the same number of items. An element of the expression list can be omitted, in which case it is treated as NULL. This is not allowed in ANSI SQL.
subquery that the row or rows to be inserted consist of column values accessed by a query specification. If a column name list is not specified, the SELECT statement must query the same number of columns as there are in the table that is receiving new rows. NULLs may be included in the select expr-list for columns that are not to be assigned values. If the INSERT operation includes a column name list, values are assigned by matching cname position with position of items in the select expr-list.

Required Privileges

The following privilege rules apply to the INSERT statement:

• To insert rows into a table, a user must have the INSERT privilege on the referenced table.

• To insert rows into a table through a view, the user must have the INSERT privilege on the view. Also, the immediate owner of the view (that is, the database in which the view resides) must have the INSERT privilege on the underlying object (view or base table).

• To insert rows into a table using a query specification, the user must have the SELECT privilege for the referenced tables or views.

• When INSERT (with a condition) is executed and it requires READ access to an object for execution, the user executing this DML statement must have SELECT right to the data being accessed.

Teradata RDBMS for UNIX SQL Reference

8-193
Teradata SQL Syntax Guide

INSERT

Some Typical INSERT Error Conditions at Japanese Character Sites

Inserting Into NOT NULL Columns

Non Valid INSERT Operations

An error occurs if the operation attempts to insert:

• multibyte characters that are not valid for the current character set.

• multibyte characters that result in an odd number of bytes.

• graphic data that is greater than 16000 logical characters (one-half of maxlength) or greater than maxlength.

• a graphic string having trailing graphic pad characters into a VARGRAPHIC field, and that operation causes the row to become identical to another row (except for the number of graphic pad characters).

The following rules apply to an INSERT operation that does not

assign values for every column (field) in a new row:

• If the column is not declared as NOT NULL and no default value is declared, NULL values are inserted.

• If the column is declared as NOT NULL and no default value is declared, an error message is returned.

An INSERT operation causes an error message to be returned if: any

of the following are true.

• The operation attempts to assign a value that will result in a violation of a unique index specification.

• The operation attempts to insert a row with no value or default for a column that is defined as NOT NULL.

• The operation attempts to assign a non NULL value that violates a CHECK constraint declared for a column.

• The operation attempts to assign a value that is of a different numeric type than that declared for the column and the assigned value cannot be converted correctly.

• The operation attempts to insert a character string having trailing blanks into a VARCHAR field, and that operation causes the row to become identical to another row (except for the number of trailing blanks).
Previous << 1 .. 158 159 160 161 162 163 < 164 > 165 166 167 168 169 170 .. 241 >> Next