Bulk insert in oracle

Question

I need to insert the huge records that are comes as Interface file(text files). Now am using this format to insert records.

INSERT ALL
  INTO POSTAL_CODE( postal_code,desc)
    VALUES('100','Coimbatore')
  INTO POSTAL_CODE (postal_code,desc)
    VALUES('101','Mumbai') SELECT * FROM DUAL;

But this gives bad performance. I am new to database. So please help me to make faster inserting records. But in db2 this format is supports.

INSERT INTO POSTAL_CODE( postal_code,desc)
    VALUES('100','Coimbatore'), (postal_code,desc),('101','Mumbai');

But why oracle is not support this type of insert. Please help me. Am stuck with this. I need to use another solution for this and that should be faster....

possible duplicate of [Best way to do multi-row insert in Oracle?](http://stackoverflow.com/questions/39576/best-way-to-do-multi-row-insert-in-oracle) — Ozan, Sep 16 '15 at 13:40
How many rows are inserted at one time? If it's more than a few hundred you may be running into the same problem as in [this question](http://stackoverflow.com/q/11656026/409172). The solution is to break the statement into a few hundreds rows per run, and convert `INSERT ALL` into `UNION ALL` like in Rahul's answer. — Jon Heller, Sep 17 '15 at 04:17

score 1 · Accepted Answer · edited May 23 '17 at 12:02

You can change the below statement

INSERT INTO POSTAL_CODE( postal_code,desc) VALUES('100','Coimbatore'),
(postal_code,desc),('101','Mumbai');

To be like below using UNION which should work in Oracle as well

INSERT INTO POSTAL_CODE( postal_code,"desc") 
select '100','Coimbatore' from dual
union all
select '99','Goa' from dual
union all
select '101','Mumbai' from dual;

You should rather check the utilities provided by Oracle for this purpose like SQL*Loader

As well check this other SO post Loading data from a text file to a table in oracle

Bulk insert in oracle

1 Answers1