-
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
-
-
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
html code is off
-
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
<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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|