-
SQL Loader to insert records into master and detail tables
Hi All,
Can I use SQL Loader to insert records from a flat file into master and detail tables. I am having rows in the flat file for child table.
I have to insert one master record which will contain filename and time of upload and these details will go in master table. Detail table will have all records of flat file.
Also I would need to load them fast. Please let me know
Thanks in advance
With Regards
Hitesh Parikh
-
It is preferrable to use external tables to load data rather than SQL Loader if you are using Oracle 9i and above.
Check the link below:
http://asktom.oracle.com/pls/ask/f?p...6611962171229,
Cheers!
OraKid.
-
Depends on how many records you are inserting. For Medium size of 100,000 to 500,000 external table concept is fine. It you are trying to load 50 million records SQL*loader will be good with direct=true option. It is going to write blocks directly.
Other concept is Oracle assumes each record as one insert statement. So it will be slow for very large loads. We can test this, if we have very big text file.
Raghu
-
Thanks a lot friends,
Ya external table is a good option but i am having oracle 8.1.5, so that option is not feasible at this juncture. Direct = true is a good option to load data fast.
My other query is, while loading data can i load master and detail data simaltaneously into Parent/Child table, i only want to insert one row in parent table and to load all records of flat file into child table.
Parent table will have a file_name, upload_Date and child table will have all records of flat file.
Since i am calling this sqlloader from Shell scripts i can not write insert statement for parent table before loading, because once i call shell scripts i am not having any control on sqlloader thread.
Can u suugest me how to fulfill my requirement via SQLloader to load in Parent and Child table.
Thanks in advance
With Regards
Hitesh Parikh
-
You can load text files into more than one table. And how you are calling does not matter. Any way you are preparing .ctl file and calling that from shell programs.
try meta link.. there are some scripts that matches your requirment.
Raghu
-
Thanks Raghud,
I got one script in Metallink simillar to my requirements.
With Regards
Hitesh Parikh
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|