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
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.
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.
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.
Reply With Quote
Bookmarks