Thursday, 22 January 2015

Bulk data loading OR Batch insert in Oracle

Step 1:

Create a table say foo, in which you want to batch insertion.

create table foo(id number(5), name varchar2(10));

Step 2: 

You can save below file with any of extension like test.tbl or test.txt or test.dat

1|Pankaj
2|Abhishek
3|Rajat
4|Naveen
5|Krishna
6|Anand
7|Mayank
8|Ravi
9|Raghu
10|Ankur

Step 3:

Create a control file.
test.ctl


LOAD DATA
INFILE 'C:\oracle_11g\app\oracle\product\11.2.0\server\md\admin\test.tbl'
INTO TABLE foo
FIELDS TERMINATED BY '|'
(id, name)

Note :
If you don't have any idea about control file then create a copy of default control file(.ctl) located in C:\oracle_11g\app\oracle\product\11.2.0\server\md\admin folder and modify it according to your need.

Step 4:

cmd:>sqlldr username/password control=<absolute path of the control file>



If the same results you are getting as above means you have successfully inserted the number of records in the table foo.

Keep updating me with your suggestions and queries in the comments below...everything will be appreciated..Thanks...!!