SQL Loader : Load into 2 Tables from 1 controlfile and 1 CSV

Question

Table T1 Structure:col1 number,col2 number

Table T2 Structure: col1 number,col2 number,col3 number

csv file:

row1:1,2,3,4,5,6
row2:1,2,3,4,5,6

I want data in Table T1 as:

col1   col2
=====  ====
1       3
1       3

I want data in Table T2 as:

col1   col2   col3
=====  ====   ==== 
1       3       5
1       3       5

following control file is not working

load data
infile *
insert into table T1 fields terminated by ',' TRAILING NULLCOLS 
(col1,fill1 filler,col2)
insert into table T2 fields terminated by ',' TRAILING NULLCOLS
(col1,fill2 filler,col2,fill3 filler,col3)
begindata
1,2,3,4,5,6
1,2,3,4,5,6

Please help me to solve this problem.

Answer 1

Try this:

LOAD DATA
INFILE *
INSERT INTO TABLE T1 FIELDS TERMINATED BY ',' TRAILING NULLCOLS 
(COL1, FILL2 FILLER, COL2, FILL4 FILLER, FILL5 FILLER, FILL6 FILLER)
INSERT INTO TABLE T2 FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(COL1, FILL2 FILLER, COL2, FILL3 FILLER, COL3, FILL6 FILLER)
BEGINDATA
1,2,3,4,5,6
1,2,3,4,5,6

Answer 2

This does work but I don't like this solution. I prefer to use external tables in these situations. The short answer is you were having problems because SQLLDR doesn't automatically re-scan the data on multiple row imports. A snippet from the documentation follows.

From the SQLLDR documentation

Using POSITION with Multiple Table Loads

In a multiple table load, you specify multiple INTO TABLE clauses. When you specify POSITION() for the first column of the first table, the position is calculated relative to the beginning of the logical record. When you specify POSITION() for the first column of subsequent tables, the position is calculated relative to the last column of the last table loaded.

Thus, when a subsequent INTO TABLE clause begins, the position is not set to the beginning of the logical record automatically. This allows multiple INTO TABLE clauses to process different parts of the same physical record. For an example, see Extracting Multiple Logical Records.

A logical record might contain data for one of two tables, but not both. In this case, you would reset POSITION. Instead of omitting the position specification or using POSITION(*+n) for the first field in the INTO TABLE clause, use POSITION(1) or POSITION(n).

    load data
    infile * 
    truncate
    into table T1 
    fields terminated by ',' trailing nullcols
    ( col1  
    , fill1 filler
    , col2  )
    into table T2 
    fields terminated by ','  trailing nullcols
    ( col1 position(1) 
    , filler filler
    , col2    
    , filler2 filler
    , col3 )   

BEGINDATA
1,2,3,4,5,6
1,2,3,4,5,6

SQL> select * From t1;

      COL1       COL2
---------- ----------
         1          3
         1          3

SQL> select* From t2;

      COL1       COL2       COL3
---------- ---------- ----------
         1          3          5
         1          3          5