This discussion is archived
6 Replies Latest reply: Jan 22, 2013 1:29 PM by User286067 RSS

Load a single data file into multiple tables

KesavarapuGanesh Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points