SQL Loader filter data into multiple tables

Here we would show how to filter data to multiple tables based on conditions.

Before you start have a look at the beginner tutorial

The control file is decribed below:


load data
infile 'load_4.txt'
discardfile 'load_4.dsc'
insert
into table test_loader_4_a
when field_2 = 'Fruit'
(
field_1 position(1) char(8),
field_2 position(9) char(5)
)
into table test_loader_4_b
when field_2 = 'City'
(
field_1 position(1) char(8),
field_2 position(9) char(5)

Things to Note:

1. Data is channeled into 2 tables test_loader_4_a and test_loader_4_b based on the condition of the field_2.

Practice:

Create 2 tables
test_loader_4_a and test_loader_4_b with varchar2 fields field_1 and field_2.

create a load_4.txt file with the data

Banana Fruit
Lemon Fruit
Tokyo City
Avocado Fruit
Boston City
Ford Car
Pear Fruit

The batch file loaddata.bat looks

SQLLDR scott@tnsname/password CONTROL=load_4.ctl, LOG=load_4.log, BAD=load_4.bad, DISCARD=load_4.dis
pause

Keep the control file, text file and the batch file in the same folder and execute the loaddata.bat file.

You will find data channeled into 2 tables based on the condition.