Oracle: SQL Loader - insert records into one or another table

Create destination tables

Create the two tables thar are to be filled:

create table tq84_sql_loader_4_a (
  field_1       varchar2(10),
  field_2       varchar2(10)
);

create table tq84_sql_loader_4_b (
  field_1       varchar2(10),
  field_2       varchar2(10)
);

Github repository Oracle-Patterns, path: /SQLLoader/ex_04/create_table.sql

Control file

The control file.

It contains two insert table statements so that two tables can be filled according to a criteria.

load data
infile 'load_4.dat' "str '\r\n'"
discardfile 'load_4.dsc'
insert
into table tq84_sql_loader_4_a
when field_2 = 'Fruit'
(
  field_1      position(1) char(8),
  field_2      position(9) char(5)
)
into table tq84_sql_loader_4_b
when field_2 = 'City'
(
  field_1      position(1) char(8),
  field_2      position(9) char(5)
)

Github repository Oracle-Patterns, path: /SQLLoader/ex_04/load_4.ctl

The data file

The name of this data file is specified with the infile statement in the control file.

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

Github repository Oracle-Patterns, path: /SQLLoader/ex_04/load_4.dat

Load the data

$ sqlldr control=load_4.ctl userid=rene/password@ora.server.xy

Discarded records

One record met neither criteria. Is is written into the discard file (load_4.dsc):