SQL Loader to insert records into master and detail tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: SQL Loader to insert records into master and detail tables

Hybrid View

  1. #1
    Join Date
    Jul 2003
    Location
    India
    Posts
    12

    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

  2. #2
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    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.

  3. #3
    Join Date
    Aug 2001
    Posts
    267
    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

  4. #4
    Join Date
    Jul 2003
    Location
    India
    Posts
    12
    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

  5. #5
    Join Date
    Aug 2001
    Posts
    267
    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

  6. #6
    Join Date
    Jul 2003
    Location
    India
    Posts
    12
    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
  •  


Click Here to Expand Forum to Full Width