How to load data using SQL Loader (SQL Loader tutorial)

SQL*Loader is a powerful and easy way to load data into database tables from external files.

We will cover the basics of SQL*Loader with a simple example.

STEP 1: Create your table (if not done already)

STEP 2: Create folder structure as shown below

STEP 3: Create the control file for your table in the folder CTL

STEP 4: Prepare your data file as per the format (delimiter etc.,) in your control file in the folder DATA

STEP 5: Create the batch file to run SQL*Loader command with required parameters in the folder BATCH

STEP 6: Run the batch file and verify the data in the table.

STEP 7: Check the log file, bad file and discard for any rejects/errors.

Below shown is the folder structure I have for my SQL*Loader.

     

        

Scroll down for example.

EXAMPLE:

Step 1: Create a table

CREATE TABLE EMPLOYEE (

         ID NUMBER(20) PRIMARY KEY,  

         NAME VARCHAR2(200), 

         DESIGNATION VARCHAR2(200)

);

Step 2: Create folder structure as shown in the above picture

Step 3: Create control file in the folder CTL

LOAD DATA

append

INTO TABLE employee

FIELDS TERMINATED BY "|"

TRAILING NULLCOLS

(

ID,

NAME,

DESIGNATION

)

Step 4: Create sample data file in the folder DATA

dat.txt

1|sam|GM

2|Mike|VP

3|Bob|CEO

4|Bart|CTO

5|Jake|HRM

6|Dean|FH

Step 5: Create batch file in the folder BATCH

Use the below command in your batch file without line breaks.

sqlldr userid=user_id/password@database control='PATH\CTL\load.ctl' data='PATHDATA\dat.txt' log='PATH\log\log.txt' bad='PATH\bad\bad.txt' discard='PATH\DISCARD\dis.txt'

NOTE: Replace PATH with your local path & database details with your's

STEP 6: Run the batch file and verify the data in the table.

          

STEP 7: Check the log file, bad file and discard for any rejects/errors.

COMMENT BELOW YOUR QUESTIONS :)