1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Conditional BULK INSERT

Discussion in 'SQL PL/SQL' started by kiran.marla, Jul 18, 2012.

  1. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi,

    Can any one explain how the conditional bulk insert is done.

    The traditional forall insert syntax is

    Code (SQL):
    FORALL j IN 1..n
    INSERT INTO employee_salaries VALUES salaries(j);
    it will insert all the values into the table.

    but i want to insert the records where sal less than 5000 with using forall statement.

    pls help me on this issue.

    Many Thanks in advance.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Then you need to refine your query to return ONLY those records where salary is less than 5000 as forall won't allow a WHERE clause.
     
  3. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Kiran,

    Have you tried using above forall (OP) syntax to insert 1 to 10 numbers into employee_salaries. I have tried using it but its not working for me. Can please post exact statement what you are trying to do.
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi David & Bhagat,

    Exact requirement as follows.

    I am developing Item Import conversion program loads data from staging table to interface table.

    For staging table i have created with necessary columns with status_flag and error_msg.

    Necessary validations is done. The records which are through validations, should insert into interface table and the same should update the staging table's status flag and error msg to Y and Inserted record. Only the valid records should insert into interface table.

    The records which are not valid records should update the same to staging table status flag and error msg to N and Not inserted.

    This is done through normal cursors which takes long time. So i choose plsql tables.

    Thanks in advance.
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    For All wont support where clause.

    The approach could be re-engineered slightly. Store all the "valid" records in One collection and "Invlaid" record in another collection. Then update the tables accorrdingly.
     
  6. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi kiran,

    Please look into the below document which explains entire process for item conversion and which explains for loop as well in coding.

    Item Conversion
     
    kiran.marla likes this.
  7. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi Bharat,

    Thanks for sharing such a good document and even i too completed the developing item conversion program. My question is how to achieve with collections said above.