Home » RDBMS Server » Server Utilities » How to use SQLLoader to load multiple tables from multile files. (SQL*Loader)
How to use SQLLoader to load multiple tables from multile files. [message #376512] Wed, 17 December 2008 12:00 Go to next message
neal.pressley
Messages: 9
Registered: December 2008
Junior Member
How to use SQLLoader to load multiple tables from multile files.


I have multiple CSV files, each representing data from one table. I am successful in loading one of them in one table using sql loader. Now, I need to know instead of repeating the same process for each of the tables, is there a way, I can put all the loading thru a single control file.

I did google and few sites talk about using "when" clause and position to dynamically decide which data to go to which table.

But my case is different..

fileA ->> tableA
fileB >> tableB
....

and so on.. I do not need any when clause.. I am just trying to avoid many control files and wants a way to say everything which need to be done in one simple command.

Surprisingly Oracle documents do not talk about such scenario.


In a big migration project, do you create control files for each and every table separately?
Re: How to use SQLLoader to load multiple tables from multile files. [message #376516 is a reply to message #376512] Wed, 17 December 2008 12:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I need to know instead of repeating the same process for each of the tables

Why?
Just write a script that contains all the sqlloader processes and call it, it is then just one command.

Quote:
Surprisingly Oracle documents do not talk about such scenario.

Because I think it is useless.

Regards
Michel
Re: How to use SQLLoader to load multiple tables from multile files. [message #376521 is a reply to message #376516] Wed, 17 December 2008 12:32 Go to previous messageGo to next message
neal.pressley
Messages: 9
Registered: December 2008
Junior Member
Let me explain again, what I was trying to say.


To migrate a table - table1, I create a dump, data1.csv, create a control file table1.ctl and then run this command:

sqlldr db control=table1.ctl data=data1.csv LOG=log1.LOG bad=bad1.bad

Now, if I have 5 tables, I will be creating five separate control files and run the above command with different parameters 5 times.

This is what I understood by going thru online materials and I am trying to find out is there any other way where you can use a single control file and specify all the tables and datafiles.

Michelle, can you please give me an example of a script which you referred. I need something which takes data from different files and laods in different tables.

Neal
Re: How to use SQLLoader to load multiple tables from multile files. [message #376526 is a reply to message #376521] Wed, 17 December 2008 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sqlldr db control=table1.ctl data=data1.csv LOG=log1.LOG bad=bad1.bad
sqlldr db control=table1.ctl data=data1.csv LOG=log1.LOG bad=bad1.bad
sqlldr db control=table1.ctl data=data1.csv LOG=log1.LOG bad=bad1.bad
sqlldr db control=table1.ctl data=data1.csv LOG=log1.LOG bad=bad1.bad
sqlldr db control=table1.ctl data=data1.csv LOG=log1.LOG bad=bad1.bad
sqlldr db control=table1.ctl data=data1.csv LOG=log1.LOG bad=bad1.bad

Replace 1 by 2,3,4,5
Put this in a file and you have the script.

Regards
Michel
Re: How to use SQLLoader to load multiple tables from multile files. [message #376531 is a reply to message #376521] Wed, 17 December 2008 13:04 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
neal.pressley wrote on Wed, 17 December 2008 13:32

trying to find out is there any other way where you can use a single control file and specify all the tables and datafiles.



In theory yes. You can have multiple INTO {table} clauses in a control file, each using a separate WHEN clause. You would have to identify the records with something that the WHEN clause can identify to funnel the data to the correct table.
Re: How to use SQLLoader to load multiple tables from multile files. [message #376536 is a reply to message #376531] Wed, 17 December 2008 13:40 Go to previous messageGo to next message
neal.pressley
Messages: 9
Registered: December 2008
Junior Member
Joy_division, what exactly, I was looking for that in a single control file, I say load data from file1 to tabel1 and file2 to table2.. and looks like we do not have option for that. Using "When" we can say load records if it matches certain criteria to table 1 but if it matches another criteria load to table2.



Michel, The one you suggested is okay but its automating only the firing of commands. Internally we have as many control files as many tables.



If any body has experience in migrating database with hundred of tables, please advise how did you proceed. Did you write individual control files for each table?
Re: How to use SQLLoader to load multiple tables from multile files. [message #376537 is a reply to message #376536] Wed, 17 December 2008 13:48 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
IMHO,
using sqlldr is not great option to migrate. You have to deal with
data inconsistencies, format problems etc.
From the previous post, I suppose you are migrating from MySQL to Oracle?
Did you try Oracle migration workbench?
>>Internally we have as many control files as many table
The control files can be dynamically created from dictionary.
If the data to be loaded is straight forward, it might work.
But it is unlikely so, specifically if data is coming from non-oracle databases.
Previous Topic: How to Import .txt files in ORACLE having 300 thousand records
Next Topic: dbms_datapump - data_filter not working
Goto Forum:
  


Current Time: Fri Dec 02 14:03:09 CST 2016

Total time taken to generate the page: 0.06375 seconds