Oracle - Bulk Insert

Bulk insert takes less time to perform operation than conventional insert.
We perform insert operation conventionally.
Example for conventional insert:-
Insert into table values (V1, V2…, Vn);

In the above query, considering there are n columns in table and the values are mentioned with out column order, so inserts values based on column_id order.

It could also be like,

Insert into table (C1, C2…, Cn) values (V1, V2…, Vn);
So the values V1 will be inserted into row and field name C1. Similarly for all columns and values respectively.

So, definitely the number of columns should match with number of values and the corresponding data type of columns should match with the values at that index.

Where as in case of bulk insert it is same as with conventional insert, but here we try to insert bulk amount of rows in one single query than writing multiple insert statements for multiple inserts.

This is possible only if we have data organised in rows and values at specific columns.

Ex:-

Say there is a table tbl1 having three columns.

Col1

Col2

Col3

Then bulk insert could be possible when data that is been to be inserted, is as below structure

Ab	cd	ef
Gh	ij	kl
Mn	xy	tu

So, if data is organised as table with rows in it, bulk insert is possible.

Obviously to organise data in table structure, it can be done using “select” query.
“Select” query can be performed on a data base table.

Finally, bulk insert can be used on a table by using result set from a select query.

This is helpful when we try to copy all rows of a table to another table, provided both having same table structure (same number of columns and data types).

Example:-
To copy rows from tbl1 to tbl2 (tbl1 and tbl2 have same columns and data types).

Insert into tbl2 select * from tbl1
Also, If a select query returns data which satisfies the table structure of the destination, bulk insert can be performed from result set of “Select” query to destination table.