Writing the Teradata MultiLoad Job Script - MultiLoad

Teradata® MultiLoad Reference - 20.00

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
Lake
VMware
Product
MultiLoad
Release Number
20.00
Published
October 2023
ft:locale
en-US
ft:lastEdition
2024-03-18
dita:mapPath
lji1691576359330.ditamap
dita:ditavalPath
kju1619195148891.ditaval
dita:id
sga1478609868891
Product Category
Teradata Tools and Utilities

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

.logtable inslogtable;
.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 )
    INDEX( Name );

.begin import mload tables employee;
.layout inslayout;
    .field EmpNo 2 char(9);
    .field Name 12 char(12);
    .field DeptNo 25 char(3);
    .field PhoneNo 29 char(4);
    .field JobTitle 34 char(12);
    .field Salary 47 char(9);
    .field YrsExp 57 char(2);
    .field DOB 60 char(11);
    .field Sex 72 char(1);
    .field Race 74 char(1);
    .field MStat 76 char(1);
    .field EdLev 78 char(2);
    .field HCap 81 char(2);
.dml label insdml;
insert into employee.*;

.import infile insert.input
     format text
     layout inslayout
     apply insdml;
.end mload;
.logoff;

Comments

  1. For syntax and descriptions of the following Teradata MultiLoad utility commands used in the preceding example, see Teradata MultiLoad Commands.
    • BEGIN MLOAD
    • BEGIN MLOAD
    • DML LABEL
    • END MLOAD
    • FIELD
    • IMPORT
    • LAYOUT
    • LOGOFF
    • LOGON
    • LOGTABLE
  2. The CREATE TABLE statement creates a new table on the database:
    • Named employee
    • Containing the following columns:

      - DeptNo

      - DOB

      - EdLev

      - EmpNo

      - HCap

      - JobTitle

      - MStat

      - Name

      - PhoneNo

      - Race

      - Salary

      - Sex

      - YrsExp

    • Indexed by:

      - EmpNo (UNIQUE PRIMARY)

      - Name

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

  3. The LAYOUT command and the series of FIELD commands specify each field of the data records that are sent to the database. (The insert.input data file created in Creating the Source Data File.)

    The specified layout name, inslayout, is referenced in the subsequent Teradata MultiLoad IMPORT command.

    Each FIELD command specifies the name, starting position, and data type description for each field in the input data records. In making the field declarations, note that the 1-character and multi-character delimiter fields are optional. They are not required in the example.

  4. The DML LABEL command introduces the INSERT statement.
  5. The IMPORT command starts the import task, using the insert.input file and the insdml insert statement.