6 Replies Latest reply: Jan 22, 2013 3:29 PM by User286067 RSS

    Load a single data file into multiple tables

    KesavarapuGanesh
      Hi,

      I am using sql loader to load the data from CSV file into the data base

      My CSV file structure is as below
      empno,ename,description,deptno,deptname
      101,john,mobilecompany,201,mobilestore
      102,smith,faxcompany,202,faxstore
      103,adams,cybercafe,203,internetstore

      In the CSV file first columns are empno,ename,description,deptno,deptname

      the above data needs to be inserted into the separate table (emp_info and dept_info) like below

      emp_info
      empno ename description
      101 john mobilecompany
      102 smith faxcompany
      103 adams cybercafe


      dept_info
      deptno deptname      description
      201     mobilestore     mobilecompany
      202     faxstore          faxcompany
      203 internetstore cybercafe

      like this needs to insert the data using sql loader

      my table structures
      ===============

      emp_info

      empno number(5)
      ename varchar2(15)
      description varchar2(15)

      dept_info

      deptno number(5)
      deptname varchar2(15)
      description varchar2(15)

      This is the CTL file i have created and which is working fine. But, the problem is, it is inserting data only when i am using all the columns specified in the csv file with filler option in the CTL file.

      CTL file
      ========

      OPTIONS
      (SKIP=1)
      LOAD DATA
      INFILE 'd:\sqlldr\task.dat'
      APPEND
      INTO TABLE emp_info2
      FIELDS TERMINATED BY ","
      TRAILING NULLCOLS
      (
      emp_num,
      emp_name,
      description
      )

      INTO TABLE dept_info2
      FIELDS TERMINATED BY ","
      TRAILING NULLCOLS
      (
      emp_num FILLER POSITION(1),
      emp_name filler,
      description,
      department_num,
      department_name,
      )

      Please help me out to achieve this with best option.

      Thanks & Regards,
      Ganesh

      Edited by: 865375 on Jan 22, 2013 1:06 AM