Teradata MultiLoad – Part 3



A Sample Teradata MultiLoad script

/*Section 1*/
.LOGTABLE TERADATA.STUDENT_log;
/*Section 2*/
.logon IP_Address/username,password;

/*Section 3*/
.BEGIN IMPORT MLOAD
         TABLES       TERADATA.STUDENT
         WORKTABLES   TERADATA.STUDENT_log_wt
         ERRORTABLES  TERADATA.STUDENT_log_et
                                      TERADATA.STUDENT_log_uv
AMPCHECK ALL;

/*Section 4*/
.LAYOUT STUDENT_SRC;
.FIELD  ROLL_NO        *  VARCHAR(20) ;
.FIELD  FIRST_NM      *  VARCHAR(20) ;
.FIELD  LAST_NM       *  VARCHAR(20) ;

/*Section 5*/
.DML LABEL Insert_Add;
INSERT INTO TERADATA.STUDENT
(
 ROLL_NO  = :ROLL_NO                                 
,FIRST_NM = :FIRST_NM                                
,LAST_NM  = :LAST_NM                              
);

/*Section 7*/
.IMPORT INFILE C:\Student_data\Student_info LAYOUT STUDENT_SRC  FORMAT VARTEXT ‘|’ APPLY Insert_Add;

/*Section 8*/
.END MLOAD;
.LOGOFF;

Section 1- In this section, we need to specify the log table name which will be useful during restarts process.  You can specify the same as the target table name or some other database as well.

Section 2- We need to provide logon information like tdpid or ip_address of Teradata system, username, password.

Section 3- In this section you must tell Teradata which tables to use. To do this, you use the .BEGIN IMPORT MLOAD command. Optionally, you can provide the name of the error tables and work table. By default, MultiLoad will create these tables automatically.

Section 4- You need to inform MultiLoad about the structure of source file using .LAYOUT command. An asterisk is placed between column name and data type to calculate the next byte in the record.

Section 5- The .DML LABEL names and defines the SQL that is to execute. It is like setting up executable code in a programming language, but using SQL.

Section 6- We need to specify here the input file path and the file name. Then we list format type as VERTEXT, LAYOUT to apply and the separator used in the source file.

Section 7- Teradata ends the MLoad here and logoff from the Teradata system.