469,304 Members | 1,800 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,304 developers. It's quick & easy.

Multiple rows insertion in single insert query.

sumittyagi
202 Expert 100+
Hi All,
Is it possible in oracle to insert multiple rows in a single insert query, as is possible in sql server:
insert into test_table values ('abc', 'xyz'), ('mno', 'pqr');

This syntax is not supported in oracle. Is oracle having any alternative for this one.

Thanks & happy new year in advance.
Dec 27 '07 #1
10 17989
debasisdas
8,127 Expert 4TB
try to use bulk insert using PL / SQL
Dec 28 '07 #2
sumittyagi
202 Expert 100+
try to use bulk insert using PL / SQL
Hi debashisdas,
Thanks for your reply. I know that can be done by PL/SQL, but in that case there will be multiple insert queries. I was wondering if there is any alternative in oracle for Multiple rows insertion specifically with single insert query.

Thanks.
Dec 28 '07 #3
The syntax you are asking about is not supported in Oracle.
Dec 28 '07 #4
sumittyagi
202 Expert 100+
The kind of syntax you are asking in SQL Server is not suppoerted in Oracle.
Thanks for your replies.
Jan 7 '08 #5
Hi All,
Is it possible in oracle to insert multiple rows in a single insert query, as is possible in sql server:
insert into test_table values ('abc', 'xyz'), ('mno', 'pqr');

This syntax is not supported in oracle. Is oracle having any alternative for this one.

Thanks & happy new year in advance.
Multiple rows can be inserted using only one insert statement only when you have got the data available in some other table in the database. In that case you can use following query:

Expand|Select|Wrap|Line Numbers
  1. insert into table1
  2. select * from table2
assuming that the structures of table1 and table2 are the same.
Jan 8 '08 #6
debasisdas
8,127 Expert 4TB
Multiple rows can be inserted using only one insert statement only when you have got the data available in some other table in the database. In that case you can use following query:

Expand|Select|Wrap|Line Numbers
  1. insert into table1
  2. select * from table2
assuming that the structures of table1 and table2 are the same.
yes that is one option ,but the user was not certainly asking about that.
Jan 8 '08 #7
Use triggers for insert or write PL/SQL for multiple insertition.
Jan 9 '08 #8
hi dear
if u want to insert the multiple values with single insert statement
then try this
insert into tablename values('&column1',&column2,.......);

actually column1 is in quotes because it is varchar or char
for number use the simple like column2
for date field u have to use like column1

on execution of statement it will ask to enter the values untill u will enter.

i hope it will help u.
Jul 9 '08 #9
Pramma
2
Yes. It is possible in Oracle.
Follow the query like

insert all
into table1 values(.,.,),
into table2 values(.,.,),
into table3 values(.,.,),
...
..
select * from dual;
Jul 16 '08 #10
Pramma
2
If u want to insert into same table multiple row, keep the tablename same in the above query. thats it.
Jul 16 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Eric Kincl | last post: by
3 posts views Thread by gregory.sharrow | last post: by
5 posts views Thread by Robert Brown | last post: by
2 posts views Thread by Diego | last post: by
24 posts views Thread by Henry J. | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.