Multiple rows insertion in single insert query.

[sumittyagi]

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.

[debasisdas]

try to use bulk insert using PL / SQL

[sumittyagi]

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.

[chritzuk]

The syntax you are asking about is not supported in Oracle.

[sumittyagi]

The kind of syntax you are asking in SQL Server is not suppoerted in Oracle.

Thanks for your replies.

[vikas000000a]

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:

insert into table1
select * from table2

assuming that the structures of table1 and table2 are the same.

[debasisdas]

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:

insert into table1
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.

[vijay dev]

Use triggers for insert or write PL/SQL for multiple insertition.

[bondwiththebest]

hi dear
if u want to insert the multiple values with single insert statement
then try this
insert into tablename values('&column 1',&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.

[Pramma]

Yes. It is possible in Oracle.
Follow the query like

insert all
into table1 values(.,.,),
into table2 values(.,.,),
into table3 values(.,.,),
...
..
select * from dual;

[Pramma]

If u want to insert into same table multiple row, keep the tablename same in the above query. thats it.