How to one data file upload into multiple table by sql loader in oracle 10g example

Question

Create table emp (id varchar2(22), name varchar2(85),hire_date date,department_id varchar2(3));



create table emp_epfo as select id emp_id,name lname,hire_date,department_id dept_id from emp where 1=2;

Control file

LOAD DATA
INFILE   '/image/ExtTableData/OnlineClaims/member_master.csv'
TRUNCATE
INTO TABLE EMP
FIELDS TERMINATED BY "," 
(ID ,NAME ,
HIRE_DATE ,DEPARTMENT_ID)
INTO TABLE EMP_EPFO
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(EMP_ID,LNAME,HIRE_DATE,DEPT_ID)

Answer 1

You can use a single control file to load data into more than one tables using SQLLDR. What you need to keep in mind is,

 LOAD DATA
    INFILE   '/image/ExtTableData/OnlineClaims/member_master.csv'
    TRUNCATE
    INTO TABLE EMP
    FIELDS TERMINATED BY "," 
TRAILING NULLCOLS
    (ID POSITION(1),
      NAME,
      HIRE_DATE,
      DEPARTMENT_ID
    )
    INTO TABLE EMP_EPFO
    (EMP_ID POSITION(1),
     LNAME,
     HIRE_DATE,
     DEPT_ID
     )

The reason for specifying POSITION(1) after the columns for separate tables is to instruct Oracle to start reading data from first row.

Quoting from Oracle documentation:

When the POSITION parameter is not used, multiple INTO TABLE clauses process different parts of the same (delimited data) input record, allowing multiple tables to be loaded from one record. When the POSITION parameter is used, multiple INTO TABLE clauses can process the same record in different ways, allowing multiple formats to be recognized in one input file.