CODEWITHSUNDEEP
oracle-databasedb2bulkinsert
user5122076
user5122076

Reputation:

Bulk insert in oracle

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....

Upvotes: 0

Views: 157

Answers (1)

Rahul
Rahul

Reputation: 77856

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

Upvotes: 1

Related Questions