can we insert multiple rows using single statement ?

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

a pl/sql block :)

Hi pwoneill,

could you please expand that pl/sql block


SrinivasM

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.

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

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.

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

Hi Pwoneill,

i mean all the rows are random

SrinivasM

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


begin
for i in 1..10 loop
insert into table values(i);
end loop;
end;


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.

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.

Either I don't understand your question or you have phrased it wrongly.

If you are attempting to insert multiple values at the same time,
do you know what you are trying to insert?

DO I know the value ahead of time?
If the value you are inserting is the same or an increment of a number
you can do that using a "for loop" Like :
Begin
For i in 1..10 Loop -- Do it 10 times
Insert into test_table (name) values ('Gram2000');
END LOOP;
Commit;
END;

Are you trying to load values from another table?
if that is the case use :
Insert into test_table select username from all_users;

If you have all your data in a flat file, use sql loader. You can skip
sqlplus and you can also use direct path load which will make it
really fast.

Hope I am helping!

Thanks