INSERT | Usage Notes | Teradata FastLoad - Usage Notes - FastLoad

Teradata® FastLoad Reference

Release Number
June 2020
English (United States)
Last Update
Product Category
Teradata Tools and Utilities

The following table describes the things to consider when using the INSERT command.

Usage Notes for INSERT 
Topic Usage Notes
Required Privileges To use the INSERT statement, the user ID associated with the Teradata FastLoad job must have INSERT privilege on the specified table.
Inserting Field Values

During the insert operation, field values are inserted in the table in the order in which the columns are listed in the CREATE TABLE statement. If field values in the input data are stored in the same order as columns are defined in the CREATE TABLE statement for the Teradata FastLoad table, a list of column names does not need to be specified in the INSERT statement (for instance, INSERT INTO table1 VALUES (:f1, :f2).

When the second format of the INSERT statement is used, a list of field names is constructed from the definition of the table. During the insert operation, field names and their data types are taken from the CREATE TABLE statement and used to define the table.

The field name definitions are established in the order in which columns are defined in the CREATE TABLE statement. So, the fields in each data record must be in the same order as the columns in the definition of the table.

Using DEFINE Commands

When using the second form of the INSERT statement, use the DEFINE command to specify the name of the input data source or INMOD routine used in the Teradata FastLoad job.

If a DEFINE command that defines one or more fields before the INSERT statement is entered, Teradata FastLoad appends the field definitions to the definitions constructed from the INSERT statement.

The colon character preceding the input field name descriptions (:fieldname) indicates that a corresponding DEFINE field must exist. If the INSERT statement does not include : fieldname expressions, then Teradata FastLoad transmits the command to the database intact, without linking it with a previous DEFINE command.
Using SHOW TABLE Statements If a Teradata SQL SHOW TABLE statement is used to display the exact definition of a table, you must do so from BTEQ or another application. Teradata FastLoad does not support this statement.
ANSI/SQL DateTime Specifications The ANSI/SQL DATE, TIME, TIMESTAMP, and INTERVAL DateTime data types in Teradata SQL CREATE TABLE statements can be used. They can be specified as column/field modifiers in INSERT statements. They must be converted to fixed-length CHAR data types when specifying the column/field names in the Teradata FastLoad DEFINE command.
Using Unicode Data
Do not use the tname.* version of an INSERT statement when using Unicode data from any of the following:
  • A session with a character set name ending with _0I
  • Any session with a character set that does not support multibyte characters (for example, ASCII or EBCDIC)

In addition to the field names from the referenced tables, these functions return byte/character counts that Teradata FastLoad uses internally to construct the USING clause for the subsequent load operation. Because of the byte and character count conversions that take place when importing and exporting CHAR and VARCHAR data between a client system and the database, the internally generated USING clause does not properly reflect the structure of the input data stream.

Unicode Session Character Set Limitation For more information, see Usage Notes.