Is it possible to insert multiple records with ONE insert?

[Sina]

Hello everyone,

Is it possible to insert several records with one insert statement in Oracle 8i?

Basically instead of inserting every record with one insert in to table A, I wanted to know if I can write the insert statement in a way, so that I could insert multiple records with one insert.

i.e I have one million records that I have to insert everyso often. but it takes a long time with the insert per each record.

Thank you all.

 

[carp]

No.

So if your records are in another table or in a flat file, you might want to look at subqueries or SQL*Loader.

 

[lewisp]

...or use Pro*C or somesuch. You can code Pro*C to insert from an array of just about any size.

Are you inserting by reading from another table or from a data file?

 

[Dagon]

From 8i onwards, you can also do array inserts in PL/SQL using the FORALL syntax.

 

[pravy24]

Yes its absolutely possible to insert multiple records using a single INSERT statement. Try the following:

If you are trying to insert from another table/tables

INSERT INTO table1(field1, field2,....) (SELECT field1,field2,... FROM table2)