SQL Loader Control File WHEN Clause Example

SQL Loader Control File WHEN Clause Example

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • SQL
  • 1 min read

In SQL Loader Control File, you can use WHEN clause to tell SQL Loader to load the data only when the condition is true. Also, you can use the WHEN clause to load data into multiple tables. Below is an example.

SQL Loader Control File WHEN Clause Example

LOAD DATA
APPEND
INTO TABLE JOB_HISTORY_10
when DEPARTMENT_ID = '10'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
EMPLOYEE_ID,
START_DATE,
END_DATE,
JOB_ID, 
DEPARTMENT_ID
)
INTO TABLE JOB_HISTORY_20
when DEPARTMENT_ID = '20'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
EMPLOYEE_ID,
START_DATE,
END_DATE,
JOB_ID, 
DEPARTMENT_ID
)

See also:

This Post Has 2 Comments

  1. naresh July 27, 2021

    i have a bulk data now have to insert only 10000 records into table through sql loader how can i mention that condition in control file, please help me

  2. Mason November 18, 2021

    Why can't we use >= operator in WHEN clause ? What is the workaround to use greater than and equal to?

Comments are closed.