Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2011
    Posts
    2

    Unanswered: Query regarding BULK UPDATE and INSERT

    Hi,

    I am using Oracle 8.1 and have a requirement of dealing with lacs of rows..

    The requirement is.. we get lacs of rows as input to us (a datawarehouse project). Currently, we have a procedure which checks the base table
    with the key value. If a record exists, then it updates all the other columns (except key columns) and if not exists it inserts that new record.
    All this occurs in a cursor and hence it is taking long time.. more than 6-7 hours... I want to tune it.

    say.. interface table table1(id,name,age,occupation)
    base table is table2(id,name,age,occupation)

    my procedure is like this.

    declare
    cursor cursor1 is
    select * from table1 (where condition);
    v_id table2.id%type;
    v_rec varchar2(10);
    begin
    for ir in cursor1 loop
    begin
    select id,'N' into v_id,v_rec from table2
    where id=ir.id;
    exception
    when no_data_found then
    v_rec='N';
    end;
    if v_rec='Y' then
    Update table2
    set name=ir.name,
    age=ir.age,
    occupation=ir.occuptaion
    where id=ir.id;
    else
    insert into table2
    values(ir.id,ir.name,ir.age,ir.occupation);
    end if;
    close cursor1;
    end;


    I have tried the BULK COLLECT concepts by using Index by tables, but the problem i faced is getting individual values.
    I mean, if I use index by tables then I cannot make the UPDATE becuase we cannot reference individual columns from IBT like ir.age, ir.name etc..
    There are more than 100 columns in the table and using 100 Index by tables (one for each column) would be difficult.

    Could anyone help me by suggesting a good alternative to overcome this problem.
    I need some syntax or a pseudocode like above.. so that I can implement it in my project...

    Thanks in advance.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Not sure if MERGE is available in Oracle 8i.

    INSERT INTO table2 SELECT .. FROM table1 WHERE NOT EXISTS (SELECT 1 FROM table1, table2 ...)
    UPDATE table2 SET (...) = (SELECT ... FROM table1 WHERE EXISTS ...) WHERE EXISTS...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •