Archives of the TeradataForum

Message Posted: Wed, 10 Mar 2004 @ 20:44:12 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

There is a feature of Multiload that you can load into a table based on a value (or IS/IS NOT NULL). It will require multiple imports of the same text file (for a Multiload) since I don't think Multiload can handle multiple input layout formats (I have never tried). You would have to have 9 input layouts and DML label statements (and maybe multiple Begin/End mload).

Something Like the following:

     .BEGIN IMPORT MLOAD
         TABLES sometable1, sometable2, sometable3
         WORKTABLES  WT_sometables
         ERRORTABLES ET_sometables
                     UV_sometables
         ERRLIMIT 50
         AMPCHECK ALL;

     .LAYOUT InputLayout1;
         .FIELD  IN_TableID  * VARCHAR(20)
         .FIELD  IN_Data1    * VARCHAR(20)
         .FIELD  IN_Data2    * VARCHAR(20)

         .
         . other layouts
         .
         .

     .LAYOUT InputLayout4;
         .FIELD  IN_TableID  * VARCHAR(20)
         .FIELD  IN_Data1    * VARCHAR(20)
         .FIELD  IN_Data2    * VARCHAR(20)
         .FIELD  IN_Data3    * VARCHAR(20)
         .FIELD  IN_Data4    * VARCHAR(20)
         .FIELD  IN_Data5    * VARCHAR(20)

         .
         . other layouts
         .
         .

     .LAYOUT InputLayout9;
         .FIELD  IN_TableID  * VARCHAR(20)
         .FIELD  IN_Data1    * VARCHAR(20)
         .FIELD  IN_Data2    * VARCHAR(20)
         .FIELD  IN_Data3    * VARCHAR(20)

     .DML LABEL InsertTable1
     IGNORE DUPLICATE INSERT ROWS;

     INSERT INTO sometable1
         (
           data1, data2
         )
         VALUES
         (
           :IN_Data1, :IN_Data2
         )
         ;

     .DML LABEL InsertTable4
     IGNORE DUPLICATE INSERT ROWS;

     INSERT INTO sometable4
         (
           data1, data2, data3, data4, data5
         )
         VALUES
         (
           :IN_Data1, :IN_Data2, :IN_Data3, :IN_Data5, :IN_Data5
         )
         ;

     .DML LABEL InsertTable9
     IGNORE DUPLICATE INSERT ROWS;

     INSERT INTO sometable9
         (
           data1, data2, data3
         )
         VALUES
         (
           :IN_Data1, :IN_Data2, :IN_Data3
         )
         ;

     .IMPORT INFILE textfile.txt
         FORMAT VARTEXT '|'
         LAYOUT INPUTLAYOUT
         APPLY INSERTS WHERE In_TableID = 1;

         .
         . other imports
         .

     .IMPORT INFILE textfile.txt
         FORMAT VARTEXT '|'
         LAYOUT INPUTLAYOUT
         APPLY INSERTS WHERE In_TableID = 4;
         .
         . other imports
         .

     .IMPORT INFILE textfile.txt
         FORMAT VARTEXT '|'
         LAYOUT InputLayout1
         APPLY INSERTS WHERE In_TableID = 9;

--------------------------------------------------

IF, you were able to modify the data file such that missing fields were still delimited like:

     1 | data1 |  data2 | | |
     4 | data1 |  data2 | data3 |  data4 | data5
     1 | data1 | datat2 | | |
     9 | data1 | data2  | data3| |
     1 | data1 | datat2 | | |

Then you could do only 2 passes through the data and have only layout format (can update only 5 tables at a time):

     .LAYOUT InputLayout;
         .FIELD  IN_TableID  * VARCHAR(20)
         .FIELD  IN_Data1    * VARCHAR(20)
         .FIELD  IN_Data2    * VARCHAR(20)
         .FIELD  IN_Data3    * VARCHAR(20)
         .FIELD  IN_Data4    * VARCHAR(20)
         .FIELD  IN_Data5    * VARCHAR(20)

     .IMPORT INFILE textfile.txt
         FORMAT VARTEXT '|'
         LAYOUT InputLayout
         APPLY INSERTS WHERE In_TableID = 1;
         APPLY INSERTS WHERE In_TableID = 2;
         APPLY INSERTS WHERE In_TableID = 3;
         APPLY INSERTS WHERE In_TableID = 4;
         APPLY INSERTS WHERE In_TableID = 5;

     .IMPORT INFILE textfile.txt
         FORMAT VARTEXT '|'
         LAYOUT InputLayout
         APPLY INSERTS WHERE In_TableID = 6;
         APPLY INSERTS WHERE In_TableID = 7;
         APPLY INSERTS WHERE In_TableID = 8;
         APPLY INSERTS WHERE In_TableID = 9;

Any other ideas anyone?

Thank You,

Dara Tressler
Data Warehouse Specialist
Teradata Certified Application Developer and Advanced Certified Professional
Business Intelligence Technologies
Speedway SuperAmerica LLC

	<Prev	Next>		<<First	<Prev	Next>	Last>>