MULTILOAD - Example in Teradata

Using a single MultiLoad job, you can do a number of different import, update and delete tasks on Teradata RDBMS tables and views.
MultiLoad command IMPORT has an option FORMAT which is used to specify the format of external data source.
The format may be
Sources
• FASTLOAD
• BINARY
• TEXT
• UNFORMAT
• VARTEXT

MultiLoad Example
In this example, we will load the data from the file, fload_data, into the table, students, in the database student_info whose owner is user john. We need to edit the MultiLoad job script file, mload_script, which is under the directory where MultiLoad works on. Before executing this MultiLoad job, we first drop the table students in database student_info. Then we invoke MultiLoad utility to finish this job.
.LOGTABLE inslogtable;
.LOGON teradata/forgetcode, ****** ;
DATABASE db_school;
CREATE TABLE  students (
studentID CHAR(5) NOT NULL,
name CHAR(20) NOT NULL,
sex CHAR(1) UPPERCASE NOT NULL,
age integer CHECK (age >=5 AND age <=100) );
.BEGIN IMPORT MLOAD  TABLES students SESSIONS 2;
.LAYOUT inslayout;
.FIELD studentID 2 CHAR(5);
.FIELD name 8 CHAR(20);
.FIELD sex 29 CHAR(1);
.FIELD age 31 CHAR(2);
.DML LABEL insdml;
INSERT INTO students.*;
.IMPORT INFILE fload_data
    FORMAT text
    LAYOUT inslayout
    APPLY insdml;
.END MLOAD;
.LOGOFF;