how to load different files using multiload into different tables.

Tools

how to load different files using multiload into different tables.

hi,

how to load different files using multiload into different tables.

File name will contain target table to load  ex: 'target_tablename_timestamp.dat, each file has header.

This header row should correspond to the column names that need to be loaded in the target table. We need to perform upserts on each target table.

Please advic me on this.

5 REPLIES
Teradata Employee

Re: how to load different files using multiload into different tables.

Hi,

MLOAD could load up to 5 target tables, here is a pseudo MLOAD script to load data to 5 target tables using 5 data files:

.BEGIN IMPORT MLOAD TABLES <target table 1>,<target table 2>,<target table 3>, <target table 4>,<target table 5>;

.LAYOUT LAY1;

A set of FIELD statements;

.LAYOUT LAY2;

A set of FIELD statements;

.LAYOUT LAY3;

A set of FIELD statements;

.LAYOUT LAY4;

A set of FIELD statements;

.LAYOUT LAY5;

A set of FIELD statements;

.DML LABEL LABELA;

INSERT INTO <target table 1> VALUES <VALUES clause>;

.DML LABEL LABELB;

INSERT INTO <target table 2> VALUES <VALUES clause>;

.DML LABEL LABELC;

INSERT INTO <target table 3> VALUES <VALUES clause>;

.DML LABEL LABELD;

INSERT INTO <target table 4> VALUES <VALUES clause>;

.DML LABEL LABELE;

INSERT INTO <target table 5> VALUES <VALUES clause>;

.IMPORT INFILE <datafile1>

               LAYOUT LAY1

               APPLY LABELA;

.IMPORT INFILE <datafile2>

               LAYOUT LAY2

               APPLY LABELB;

.IMPORT INFILE <datafile3>

               LAYOUT LAY3

               APPLY LABELC;

.IMPORT INFILE <datafile4>

               LAYOUT LAY4

               APPLY LABELD;

.IMPORT INFILE <datafile5>

               LAYOUT LAY5

               APPLY LABELE;

.END MLOAD;

.LOGOFF;

N/A

Re: how to load different files using multiload into different tables.

Hi Ivyuan,

will it be a sequential load or all 5 target tables will be loaded parallely?

what will happen with the rest of the tables if load of one  of the table fails?

Regards,

Deepak

N/A

Re: how to load different files using multiload into different tables.

Waiting for the answer...can someone please reply 

Teradata Employee

Re: how to load different files using multiload into different tables.

Those 5 IMPORTs are processed sequentially.

-- SteveF
Teradata Employee

Re: how to load different files using multiload into different tables.

Hi Deepak,

Yes, those 5 IMPORTs are processed sequentially.

The MultiLoad job will terminate if loading one of the tables fails due to certain errors, the MultiLoad job could be re-submitted

after the errors are resolved.

--Ivy.