can we insert multiple rows using single statement ?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: can we insert multiple rows using single statement ?

Hybrid View

  1. #1
    Join Date
    Dec 2000
    Posts
    95

    Question

    Hi All,

    can we insert multiple rows into a table.

    suppose i have a table like follows

    SQL> create table test (name varchar2(10));

    Table created.


    and now i can insert one row with asingle statement like

    insert into test values ('vas');

    this will work fine , but i want to insert more than one row with a single statement . i think using 'values' its not possible .
    Is there any other method to do this?

    for any help thanks

    SrinivasM


  2. #2
    Join Date
    Jun 2000
    Posts
    417
    a pl/sql block :)

  3. #3
    Join Date
    Dec 2000
    Posts
    95
    Hi pwoneill,

    could you please expand that pl/sql block


    SrinivasM

  4. #4
    Join Date
    Jun 2000
    Posts
    417
    well it depends on what you mean by inserting multiple rows. explain your requirements a bit more and we can find a good way to do it.

  5. #5
    Join Date
    Dec 2000
    Posts
    95
    Hi Pwoneill,

    ok i will explain whats my problem

    i have a table with one column

    SQL> desc test;
    Name Null? Type
    ----------------------------------------------------- -------- -----------------
    NAME VARCHAR2(10)


    if want to insert a row i will insert like this

    insert into test values ('vas');
    one will be created

    like this if i want to insert 10 rows , i have to insert 10 times like above. in my case it is hitting the database (server side) 10 times. so what my question is can we insert those 10 rows with a single statement ?

    i think you got my question

    for any help thank you

    SrinivasM

  6. #6
    Join Date
    Jun 2000
    Posts
    417
    I see what you mean but are all the rows the same? Is it an incrementing value you want put in there 10 times? 10 random numbers? Etc

    A pl/sql block will still execute 10 insert statements but you can put it in a loop to make your life easier. There isn't a way to get out of inserting 10 times if you want 10 rows.

  7. #7
    Join Date
    Jan 2001
    Posts
    60
    Hi

    What u can do is ,put all insert values in a sql script and run it it's going ot excute only once .

    Since u need to insert different names there is no alternative i think .

    Thanks
    lnreddy
    html code is off

  8. #8
    Join Date
    Dec 2000
    Posts
    95
    Hi Pwoneill,

    i mean all the rows are random

    SrinivasM

  9. #9
    Join Date
    Jun 2000
    Posts
    417
    well there isn't really a random function in oracle, you can insert whatever you want into the table. a small block that just loops 10 times and inserts is

    <font face="courier">
    begin
    **for i in 1..10 loop
    ****insert into table values(i);
    **end loop;
    end;
    </font>

    that doesn't commit so you would have to put commits wherever you want, etc. that inserts 1, 2, 3 ... 10 into the table.

    The short answer to your question was that no there isn't a way to insert more than one row with one insert statement, but there are lots of ways to do it with more insert statements.

  10. #10
    Join Date
    Aug 2000
    Posts
    194
    There is something like "Bulk Binding", (I guess from oracle 8.0.5 onwards)

    You can put all ur values into an PL/SQL array and insert them at once.

    FOR j IN lower_bound..upper_bound LOOP
    var1(j) := j; -- put the values in PL/SQL array
    END LOOP;

    FORALL i IN lower_bound..upper_bound --see "FORALL"
    INSERT INTO tab1 VALUES (var1(i)) ;

    This runs much fater than the normal insert.

    Note: Not sure this is of any assistance to ur original posting.

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