| How to use SQLLoader to load multiple tables from multile files. [message #376512] |
Wed, 17 December 2008 12:00  |
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 #376521 is a reply to message #376516] |
Wed, 17 December 2008 12:32  |
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 #376531 is a reply to message #376521] |
Wed, 17 December 2008 13:04 |
joy_division
Messages: 4963
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 |
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 |
 |
Mahesh Rajendran
Messages: 10708
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
