Multiload in Teradata using a Variable-length, comma-separated file

How to load a table from Comma-separated Variable length file?

Multiload has four FORMAT to load data:

FASTLOAD;

BINARY;

TEXT;

UNFORMAT;

VARTEXT.

The following is a Teradata Multiload example:

.LOGTABLE WORK_TBLS.USER_INTERACT_ID_LOG;

.LOGON DWTEST1/USER1,PWD;

.BEGIN IMPORT MLOAD

TABLES DW_TBLS.USER_INTERACT_ID

WORKTABLES WORK_TBLS.USER_INTERACT_ID_WK

ERRORTABLES WORK_TBLS.USER_INTERACT_ID_ET

WORK_TBLS.USER_INTERACT_ID_UV;

.LAYOUT DATAIN_LAYOUT;

.FIELD INTERACT_DESC 1 VARCHAR(30);

.FIELD INTERACT_TYP * varchar(10);

.DML LABEL INSERT_DML;

INSERT INTO DW_TBLS.USER_INTERACT_ID

(

INTERACT_TYP = :INTERACT_TYP

,INTERACT_DESC = :INTERACT_DESC

);

.IMPORT INFILE /tmp/InteractionTypes.csv.tmp1

FORMAT vartext ','LAYOUT DATAIN_LAYOUT

APPLY INSERT_DML;

.END MLOAD;

.LOGOFF;


Important Points to Note:

1. The Errortables are Optional. If you do not specify the errortables, they will be created as ET_USER_INTERACT_ID and UV_USER_INTERACT_ID

But, if they are specified, they are position-dependent. So, ET and UV table may get interchanged by mistake.

Hence, if you write

ERRORTABLES
UV_USER_INTERACT_ID
ET_USER_INTERACT_ID

It will result in UV_USER_INTERACT_ID storing Error rows and ET_USER_INTERACT_ID storing UPI violation rows.

Incase you need any, just write your queries in comments section. Let us know what topics you want covered in the next blogs!!

Subscribe with us if you like our Blog!!