INSERT - FastLoad

Teradata FastLoad Reference
Product
FastLoad
Release Number
15.10
Language
English (United States)
Last Update
2018-10-07
dita:id
B035-2411
lifecycle
previous
Product Category
Teradata Tools and Utilities

Purpose  

INSERT is a Teradata SQL statement that inserts data records into the rows of the Teradata FastLoad table.

Note: FastLoad also supports temporal syntaxes like CURRENT VALIDTIME, SEQUENCED VALIDTIME, VALIDTIME, NONSEQUENCED VALIDTIME and NONTEMPORAL clauses prefixed in INSERT/INS statement.

Syntax  

where

 

Syntax Element

Description

tname

Name of the table into which rows are inserted.

cname

Name of column to receive a new row value during the insert operation.

For each cname defined, a corresponding fieldname must be specified.

A list of column names can be defined in any order. The column names do not have to be defined in the same order as they appear in the CREATE TABLE statement.

fieldname

Field name that was defined in a previous DEFINE command.

During the insert operation, Teradata FastLoad inserts the field in the input data record that was assigned to the fieldname into the corresponding column (cname) of the Teradata FastLoad table.

.*

Wildcard specification that all columns in tname that are used to construct the list of field names be used in the insert operation.

When the wildcard specification is used, Teradata FastLoad queries the Teradata Database for all of the column names and uses them to construct a valid INSERT statement.

Usage Notes

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

Table 36 describes the things to consider when using the INSERT command.

 

Table 36: 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.

Note: 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 Teradata 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

Note: Do not use the tname.* version of an INSERT statement when using Unicode data from any of the following:

  • A KATAKANAEBCDIC session
  • 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 Teradata Database, the internally generated USING clause does not properly reflect the structure of the input data stream.

    Unicode Session Character Set Limitation

    For information, see Table 35 on page 121.

    Example  

    The following command example defines the EmpRecs input data source and the fields in each record (Emp_Number and Emp_Name): 

    DEFINE Emp_Number (INTEGER), Emp_Name (VARCHAR(30)),
       FILE=EmpRecs ;
    INSERT INTO Employee (EmpNo, Name) VALUES
       (:Emp_Number, :Emp_Name) ;

    The INSERT statement defines the table and columns to receive new data values.

    For each data record, the value in the first field (Emp_Number) is inserted in the EmpNum column and the value in the second field (Emp_Name) is inserted in the Name column.

    Example  

    The following command example establishes a list of field names from the definition of the Teradata FastLoad table:

    DEFINE FILE=InFile;
    INSERT OldTable.* ;

    The DEFINE command specifies the input data source (InFile) and defines each field to be inserted in the Teradata FastLoad table (OldTable).

    Example  

    Sometimes the records in an input data source contain data that does not belong in the Teradata FastLoad table. If, for example, each record contains 100 bytes of extra data, a dummy field can be defined in the DEFINE command that is not referenced in the INSERT statement.

    The following command example constructs a list of field names that match the current definition of NewTable appended to the definition of the extra data item: 

    DEFINE ExtraData (CHAR (100)) FILE = InFile;
    INSERT NewTable.*;

    When extra data is used in the input data source with the INSERT TABLE .* feature, the extra data must be located at the beginning of each record.

    This feature cannot be used if the extra data occurs in the middle or at the end of the records. In this case, explicitly define each data item in the data source and each item in the values clause of the INSERT statement.