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

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.

From stackoverflow

• 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
  

  Quassnoi : What does it say?

  Neil Kodner : see my response(below and hopefully soon to be above). Multiple INTO clauses require you to reset the logical pointer-otherwise you see 4,5,6 go into t2. A position() command will reset the pointer. It's always easier to load positional data into multiple tables, for this very reason.

  Quassnoi : Don't have an SQL*Loader handy, so I'll just take your word for it :)

• 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