#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    1
    Rep Power
    0

    Updating two table in oracle via sql loader


    Hi,

    I apologize now if this is not the correct forum, i am new to the site.

    I am trying to write a control file that will read information from two INFILES and update two tables with the different information via SQL Loader. I am using Oracle 11g on Linux.

    However, i am not sure how to take the result from the first insert query and use it as input to the second insert query.

    Currently I have the following control file:

    LOAD DATA
    INFILE 'table1.dat'
    INFILE 'table2.dat'
    APPEND
    INTO TABLE table1
    WHEN tid='1'
    FIELDS TERMINATED BY ","
    OPTIONALLY ENCLOSED BY '"'
    (pid "pid.nextval", p_fname, p_lname, tid)

    INTO TABLE table2
    WHEN dept_loc='1'
    FIELDS TERMINATED BY ","
    OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    (dept_id, pid, dept_name, dept_loc, dept_desc)

    The pid in the second insert query should be taken from the pid generated in the first insert query. However, I do not know how to do this. Does SQL Loader run the first insert query for all entries in the table1.dat file and then the second insert query or will it do one record at a time?

    Here are my INFILES:

    table1.dat
    ,John,Doe,1
    ,Joe,Smith,1

    table2.dat
    10,,abc,1,
    11,,xyz,1,


    The second field in table2.dat should be taken from the result of creating a record in the table1.dat.

    Is this something that can be achieved using SQL Loader? Does anyone know the correct syntax for this?

    The first part of the control file is successful, I can see the table being updated with the contents on table1.dat, but the second query fails.

    Any help/pointers is greatly appreciated.

    scobie82
  2. #2
  3. Humble Learner
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2007
    Location
    Bangalore, India
    Posts
    280
    Rep Power
    120
    1. Load data in both the tables (intermediate/temporary tables) from the file using SQL Loader.
    2. Create a procedure with the logic that you wan to implement.
    3. Insert into the target table by using/executing the procedure.

IMN logo majestic logo threadwatch logo seochat tools logo