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

Teradata RDBMS forUNIX SQL Reference - NCR

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

• In ANSI mode, INSERTing character data, if in order to comply with maximum length of the target column, non-blank characters are truncated from the source data.

In Teradata mode, the above insertion is permitted (characters are truncated silently). This could result in improper strings in Japanese character machines.

The Teradata RDBMS ignores trailing blanks on character strings

when comparing values for field or row duplication.


Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide


Valid INSERT Operations

Duplicate Rows and INSERT-SELECT

The INSERT Process

Inserting Rows Through a View

An INSERT operation does not return an error message if many of the following are true.

• The operation attempts to insert a graphic or character string that is longer or shorter than that declared for the column. The string is silently adjusted and inserted. However, in ANSI mode, if the string is too long a report is generated.

• The operation uses a query and no rows are returned.

If an INSERT using a SELECT subquery will create duplicate rows, the result depends on both the table definition and the mode of the session doing the INSERT. This only applies if no UNIQUE constraints or UNIQUE indexes exist. Otherwise duplicate rows are not allowed, in any case.

• MULTISET Tables. These tables permit duplicate rows and any duplicates created are stored in the table.

• SET Tables (no duplicate rows permitted), session

in either ANSI or Teradata mode. Fully duplicate rows are |

discarded, leaving a single row image for the duplicates. No warning or error is issued even if the table includes unique indexes.

An INSERT process performs the following functions:

• Sets a write lock on the row, or on the table in the case of an INSERT with a SELECT subquery.

• Handles the entire INSERT operation as a transaction in which every value is inserted successfully or no values are inserted.

This is to prevent a partial insert from occurring.

The INSERT operation takes more processing time on a table defined with FALLBACK or a secondary index, because the FALLBACK copy of the table or the secondary index subtable also must be changed.

The following rules apply to inserting rows through a view:

• The user and immediate owner of the view must have the appropriate privileges.

• The view must reference columns in only one table or view.

• None of the columns in the view may be derived by using an expression to change values in the underlying table.

• Each column in the view must correspond one to one with a column in the underlying table or view.

• The view must include any column in the underlying table or view that is declared as NOT NULL.

Teradata RDBMS for UNIX SQL Reference

Teradata SQL Syntax Guide


INSERTs That Use Subqueries

Data Takes the Attributes of the insert’s New Table

• No two view columns may reference the same column in the underlying table.

• If the statement used to define a view contains a WHERE clause, and WITH CHECK OPTION, all values inserted through that view must satisfy constraints specified in the WHERE clause.

If a view includes a WHERE clause and does not include WITH CHECK OPTION, data may be inserted, which will not be visible through that view.

Caution should be used when granting the privilege to insert data through a view. Data in fields not known to the user might be inserted when a row is inserted through a view.

An INSERT operation that uses a subquery differs from a simple INSERT in that many rows may be inserted in a single operation, and the row values may come from more than one table.

The query specification must always include the FROM tname clause.

Also, a column name list must be defined in an INSERT statement that includes a subquery when the following conditions exist:

• The number of columns listed in the query specification differs from the number of columns contained in the table receiving new rows.

• The order of columns listed in the query specification differs from the order in which columns were defined in the table receiving the new rows.

If the column attributes defined for a new table differ from those of the columns whose data is being inserted via the INSERT statement, the data takes on the attributes of the new table.

The source column names may differ from the destination column names in the new table. The data is inserted correctly if the SELECT statement lists the source column names in the same order as the corresponding destination columns in the CREATE TABLE statement. This is true even if the new table has a column that is to contain data derived (either arithmetically or by aggregate operation) from the column data in the existing table.


Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide


The following examples illustrate the use of INSERT.


The following statement may be used to insert a row for the new Example 1 employee Smith in the Employee table:
Previous << 1 .. 159 160 161 162 163 164 < 165 > 166 167 168 169 170 171 .. 241 >> Next