CODEWITHSUNDEEP
sql-loaderbulkimport-from-csv
Mateen-Hussain
Mateen-Hussain

Reputation: 748

sqlldr insert multiple files into multiple tables with one control file

Can I use one control file to insert data into multiple tables from multiple files? Like:

load data
INFILE 'C:\Users\shu37\Desktop\emp.csv'
INTO TABLE Emp
TRUNCATE
FIELDS Terminated By ',' OPTIONALLY ENCLOSED BY '"'
(EmpId,EmpName)
INFILE 'C:\Users\shu37\Desktop\dept.csv'
INTO TABLE department
TRUNCATE
FIELDS Terminated By ',' OPTIONALLY ENCLOSED BY '"'
(DId,DName)

I have like 500 tables which I want to import data from a equally number of CSVs (one CSV per table) and I can't write 500 control files.

Even though I write I need a superscript to run all 500 control files in a loop, which seems a bit inefficient.

I'm sure there should be a way to do this with SQLDLR.

Any ideas?

Upvotes: 0

Views: 5368

Answers (2)

Abhishek jha
Abhishek jha

Reputation: 1

Here is an example of a SQL*Loader controlfile to load data into multiple tables:

LOAD DATA

INFILE /u01/app/oracle/load.dat

REPLACE

INTO TABLE emp

   WHEN empno != ' '

   ( empno POSITION(1:4) INTEGER EXTERNAL,

     ename POSITION(6:15) CHAR

)

INTO TABLE proj

WHEN projno != ' '

   ( projno POSITION(25:27) INTEGER EXTERNAL,

     empno POSITION(1:4) INTEGER EXTERNAL

)

The Oracle documentation notes:

Multiple INTO TABLE clauses allow you to:

  • Load data into different tables
  • Extract multiple logical records from a single input record
  • Distinguish different input record formats
  • Distinguish different input row object subtypes

Upvotes: 0

Gary_W
Gary_W

Reputation: 10360

No, I believe you need the data pump export/import facility instead: http://docs.oracle.com/cd/B28359_01/server.111/b28319/dp_export.htm

For reference here's the SqlLoader reference: http://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_control_file.htm

Or if you could get the data files named for the tables, you could write a program to generate the control files for each file/table and a master SQL file to call them all but that could get ugly.

Upvotes: 0

Related Questions