General discussion

  • Creator
    Topic
  • #2084506

    muliple row insertion in oracle

    Locked

    by hcbraj ·

    hello,

    i am incorporating an online transaction in a website design. the contents of an order form are to be stored in an oracle table.

    i know of single row insertion in the table and also multiple row retrieval.

    but i do not how to insertmultiple rows of data into a table at a single instance.

    please some body help by giving an idea. i would be grateful if a code snippet is given.

    i am using asp,oracle8,javascript.

    thank u

All Comments

  • Author
    Replies
    • #3774821

      muliple row insertion in oracle

      by mukul_gandhi ·

      In reply to muliple row insertion in oracle

      I think unit of insertion in Oracle is an Insert statement. So u would write a loop for multiple inserts or repeatedly perform single inserts. I feel making multiple inserts atomic in Oracle is very difficult.

      regards
      -mukul

    • #3772868

      muliple row insertion in oracle

      by don.goodman ·

      In reply to muliple row insertion in oracle

      I know of no native method in oracle which will allow you to insert multiple rows in a table in one pass.

      You could create an ado command object, add parameters to it for each column and create an array of the values to be inserted and then pass this to something like a database access component or a procedure which would loop through the array and insert the 1:M rows.

      Each row would be inserted individually the only real benefit here would be that the client(browser) could send all of the data to the server for processing in one network trip.

      This however trades reduced network traffic for increased programming complexity.

      One of our developers created a standard MTS data access component which accepts two arrays, one ofvalues and one of parameters and can do this. (In hindsight I would not have done this as it is more complexity than is needed and so far we have not needed it for multiple updates).

    • #3775659

      muliple row insertion in oracle

      by shahsu ·

      In reply to muliple row insertion in oracle

      Hi,

      It is possible to insert multiple rows into a table with a single statement. The syntax goes like this:

      Insert into TabA ( colA1, colA2, colA3 )
      select colB1, colB2, colB3 from TabB where colB1=100;

      The above statement can be used when you are inserting data from one table to another. In ur case, I believe what u need is inserting multiple rows
      into a table but the data comes from the client side. This can be implemented as below.

      Insert into TabA ( coA1, colA2, colA3 )
      select colB1, colB2, colB3 from
      (
      (select
      ‘colB1Value1’ as colB1,
      ‘colB2Value1’ as colB2,
      ‘colB31Value1’ as colB3,
      from Dual )
      UNION
      (select
      ‘colB1Value2’ as colB1,
      ‘colB2Value2’ as colB2,
      ‘colB31Value2’ as colB3,
      from Dual )
      )

      Basically what we are doing is,
      1. we are putting the values, we want to be inserted, as constants in select statement
      2. We are

    • #3781758

      muliple row insertion in oracle

      by zaq42 ·

      In reply to muliple row insertion in oracle

      With ASP and Oracle, you’re not going to have an answer as easy as we’d hope.

      BUT it isn’t too difficult.

      Just Loop over each record of the order form in the ASP target page. Create ONE insert SQL command inside that loop.

      You can use the BEGIN & COMMIT transaction functions around the loop to ensure that the all of the INSERT statements are completed.

      Feel free to email me for an example.

      Hope that helps!

      Andy (zaq42@yahoo.com)

Viewing 3 reply threads