FastLoad Job Example | Writing the FastLoad Job Script | Teradata FastLoad - 17.10 - Writing the Teradata FastLoad Job Script - FastLoad

Teradata® FastLoad Reference

Product
FastLoad
Release Number
17.10
Published
June 2021
Last Update
2021-07-01
Content Type
Programming Reference
Publication ID
B035-2411-061K
Language
English (United States)

Create and save a Teradata FastLoad job script file named flinsert.fastload that loads the six-record insert.data file into the Employee table:

sessions 2;
errlimit 25;
logon tdpid/username,password;
CREATE TABLE employee (
       EmpNo SMALLINT FORMAT ‘9(5)’ BETWEEN 10001 AND 32001 NOT NULL,
       Name VARCHAR(12),
       DeptNo SMALLINT FORMAT ‘999’ BETWEEN 100 AND 900 ,
       PhoneNo SMALLINT FORMAT ‘9999’ BETWEEN 1000 AND 9999,
       JobTitle VARCHAR(12),
       Salary DECIMAL(8,2) FORMAT ‘ZZZ,ZZ9.99’ BETWEEN 1.00 AND 999000.00 ,
       YrsExp BYTEINT FORMAT ‘Z9’ BETWEEN -99 AND 99 ,
       DOB DATE FORMAT ‘MMMbDDbYYYY’,
       Sex CHAR(1) UPPERCASE,
       Race CHAR(1) UPPERCASE,
       MStat CHAR(1) UPPERCASE,
       EdLev BYTEINT FORMAT ‘Z9’ BETWEEN 0 AND 22,
       HCap BYTEINT FORMAT ‘Z9’ BETWEEN -99 AND 99 )
       UNIQUE PRIMARY INDEX( EmpNo ) ;
set record unformatted;
define
       delim0(char(1)),
       EmpNo(char(9)), delim1(char(1)),
       Name(char(12)), delim2(char(1)),
       DeptNo(char(3)), delim3(char(1)),
       PhoneNo(char(4)), delim4(char(1)),
       JobTitle(char(12)), delim5(char(1)),
       Salary(char(9)), delim6(char(1)),
       YrsExp(char(2)), delim7(char(1)),
       DOB(char(11)), delim8(char(1)),
       Sex(char(1)), delim9(char(1)),
       Race(char(1)), delim10(char(1)),
       MStat(char(1)), delim11(char(1)),
       EdLev(char(2)), delim12(char(1)),
       HCap(char(2)), delim13(char(1)),
       newlinechar(char(1))
file=insert.input;
show;
begin loading employee errorfiles error_1, error_2;
insert into employee (
       :EmpNo,
       :Name,
       :DeptNo,
       :PhoneNo,
       :JobTitle,
       :Salary,
       :YrsExp,
       :DOB,
       :Sex,
       :Race,
       :MStat,
       :EdLev,
       :HCap
);
end loading;
logoff;

Comments

  1. For syntax and descriptions of the following commands, see Teradata FastLoad Commands:
    • SESSIONS
    • ERRLIMIT
    • LOGON
    • SET RECORD
    • DEFINE
    • SHOW
    • BEGIN LOADING
    • INSERT
    • END LOADING
    • LOGOFF
  2. The CREATE TABLE statement creates a new table on the database:
    • Named employee
    • With 13 columns:

      – EmpNo

      – DeptNo

      – PhoneNo

      – JobTitle

      – Salary

      – YrsExp

      – DOB

      – Sex

      – Race

      – MStat

      – EdLev

      – HCap

    • Indexed by EmpNo (UNIQUE PRIMARY)

      For syntax and a complete description of the Teradata SQL CREATE TABLE statement, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 and Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

  3. The DEFINE command specifies each field of the data records that will be sent to the database. (The insert.input data file was created in the Creating the Source Data File subsection.)

    Each field definition provides the name and data type description for each field in the input data records. In making the field declarations, note that the one character delimiter fields are optional. They do not need to be used in the example.

  4. The BEGIN LOADING command specifies the error files and starts the Teradata FastLoad job, using the insert.input file and the Employee table.