Abstract: This article focus on inserting millions of records in a single go in a single or multiple table.
Introduction
Relational DBMS provide us with many features which we generally are not aware of in real life, if you are lucky you will be informed by colleagues, learnt threw watching tutorial, books or else when you are stuck in day to day escalation from support team that the utility you gave for inserting is running slow.
Agenda:
Inserting a bulk upload from CSV file to the database using SQL Loader utility.
Here in the article I will inserting 2 lakh records in the table, which I was finding an overhead in my day to day development life.
Let’s get started, I have a csv files with 200999 records. In Order to insert the same.
I will follow below steps:
- Go to database and search for table( ex TblEmployee)
- Right click on table and select import data.
Figure1.0 Demonstrating Import data feature in sql developer.
- Search for the file you want to insert and click on ok.
Figure2.0 selecting your csv file.
- Following below window will open, you can define the delimiter as per your need, csv file has by default ‘,’ as a delimiter
Figure3.0 Checking the records from csv file.
Cross verify your records, if you find any redundant data that you don’t want to insert, than please correct the same in the file.
Once checked than click on Next button
Figure4.0 Select the import method.
- Select Import method as SQL loader utility as shown below:
Figure5.0 SQL Loader Utility
To remove the row limit clear the 100 value from the textbox.
Click on next:
Now in Column definition you need to map your csv headers with table column
Figure6.0 mapping csv headers with table column.
Click Next
Figure7.0 setting up configuration path for the log file and ctl file.
Now set the path for the log file, bad file and batch file, as shown below:
Figure8.0 setting up configuration path for the log file and ctl file.
Now once you are done with path configuration click on next.
Figure9.0 verifying the configuration done in the SQL loader.
Click on verify to verify the configuration, if you have mapped a two csv headers with same column, this window will give you an error.
Figure10.0 verified successfully.
Once everything is verified. Click on Finish
We can see the files are created to our configured folder:
Figure11.0 Batch File generated.
Now before executing I want to you to note down one more requirement. What if one of my column is using oracle sequences for the column value, than how should we proceed to do the same.
In ctl file generated we need to add a simple one line to achieve the same, as shown below:
load data
infile ‘C:\Users\Saillesh.pawar\Desktop\RRP_B_E_01082016new.csv’ “str ‘\r\n’”
append
into table tableName
fields terminated by ‘,’
OPTIONALLY ENCLOSED BY ‘”‘ AND ‘”‘
trailing nullcols
( EMPLOYEE_ID CHAR(4000),
FIRST_NAME CHAR(4000),
EMAIL_ID CHAR(4000),
MOBILE_NO CHAR(4000),
CITY CHAR(4000),
STATE CHAR(4000),
DEVICE_TYPE CHAR(4000),
FLAG CHAR(4000),
UD CHAR(4000),
COUPON_CODE CHAR(4000),
ADDED_DATE DATE “dd-mm-yy”
)
After adding Sequence command:
load data
infile ‘C:\Users\Saillesh.pawar\Desktop\RRP_B_E_01082016new.csv’ “str ‘\r\n’”
append
into table REFERRAL_TRANS_DATA
fields terminated by ‘,’
OPTIONALLY ENCLOSED BY ‘”‘ AND ‘”‘
trailing nullcols
( EMPLOYEE_ID CHAR(4000),
FIRST_NAME CHAR(4000),
EMAIL_ID CHAR(4000),
MOBILE_NO CHAR(4000),
CITY CHAR(4000),
STATE CHAR(4000),
DEVICE_TYPE CHAR(4000),
FLAG CHAR(4000),
UD CHAR(4000),
COUPON_CODE CHAR(4000),
ADDED_DATE DATE “dd-mm-yy”,
SR_NO “REFERRAL_TV_SR_NO.nextval”
)
Once we have resolved this problem
Let look into our one more problem what if I don’t have access to the server where db is installed and I don’t have tnsnames.ora files for the tns of database
For the same I will just pass the full tns to the batch file, I will edit the batch file and insert following commands after sqldr.
userid=’RPOS_PRODUCTION/rpos@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.01)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=tns)))’
And after all the command write pause for pausing cmd.
sqlldr userid=’RPOS_PRODUCTION/rpos@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.01)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=tns)))’
CONTROL=RRP_B_E_01082016new.ctl LOG=D:\bulkUploadSqlDeveloper\RRP_B_E_01082016new.log BAD=D:\bulkUploadSqlDeveloper\RRP_B_E_01082016new.bad skip=1
Pause
Once we are done with this we are ready to execute our batch file.
Figure12.0 Running Batch File.
After running the batch file, you will get the following output:
Figure13.0 Records are getting inserted
You can see your table records for the status of job. The SQL loader Utility helps you upload bulk data from the csv or other types of file in an easy and efficient manner.
Figure14.0 SQL loader gets completed.
Once the all the records are inserted we can check the same in our db.
Figure15.0 count reconciled with database.
Now what if we want to insert the same values into another table, then we need to add some more configuration in our cdl file below the first table as shown below:
into table tableName
fields terminated by ‘,’
OPTIONALLY ENCLOSED BY ‘”‘ AND ‘”‘
trailing nullcols
(
“Empl ID” FILLER POSITION(1) CHAR, –start from the first location of csv header
” Name” FILLER, –by pass this header
“Email ID” filler, –by pass
mobileno “Substr(:mobileno,1,10)”, first header
STATUS CONSTANT ‘Email invitation to be sent’, –inserting hardcoded values to the column
City filler, –by pass
State filler,–by pass
Type filler,–by pass
flag filler,–by pass
ud filler,–by pass
pmsg_Val ,
email_id “:pmsg_Val”, –using above value for the other columns.
employee_id “:pmsg_Val”,
pmsg_status “1”,
item_count “1”,
pmsg_id “41”,
create_dt “SYSDATE+1”,
product_id “1000461”,
expiry_date “SYSDATE+14”
)
I hope this article was useful for uploading bulk upload from csv file using SQL loader.
References
using Saillesh.Pawar; 