Multi-Table Loads

A single sqlldr run can load data into multiple tables simultaneously from one pass over the data file. This is efficient when a single feed contains interleaved record types or when you want to fan out denormalised source data into a normalised schema.

Basic structure

LOAD DATA
INFILE 'source_file.dat'
APPEND

INTO TABLE schema.table_a
WHEN condition_a
( field_mapping_for_a )

INTO TABLE schema.table_b
WHEN condition_b
( field_mapping_for_b )

sqlldr reads each record once and tests it against every INTO TABLE block's WHEN condition. A record can match zero, one, or more tables.

Fan-out: one row loads into multiple tables

When a record should be split across multiple tables (denormalization reversal):

LOAD DATA
INFILE 'orders_full.csv'
APPEND

INTO TABLE sales.orders
FIELDS TERMINATED BY ','
( order_id, customer_id, order_date DATE "YYYY-MM-DD", total_amount DECIMAL EXTERNAL,
  -- skip line_item fields
  product_id FILLER, quantity FILLER, unit_price FILLER )

INTO TABLE sales.order_lines
FIELDS TERMINATED BY ','
( order_id, product_id INTEGER EXTERNAL, quantity INTEGER EXTERNAL,
  unit_price DECIMAL EXTERNAL,
  -- skip header fields
  customer_id FILLER, order_date FILLER, total_amount FILLER )

Both INTO TABLE blocks have no WHEN clause — every row is loaded into both tables. Use FILLER on fields that don't belong to each table.

Route by record type indicator

LOAD DATA
INFILE 'crm_export.dat' "FIX 150"
APPEND

INTO TABLE crm.customers
WHEN (1:1) = 'C'
( rec_type    POSITION(1:1)    FILLER,
  customer_id POSITION(2:9)    INTEGER EXTERNAL,
  name        POSITION(10:59)  CHAR,
  email       POSITION(60:109) CHAR,
  created_at  POSITION(110:119) DATE "YYYY-MM-DD" )

INTO TABLE crm.contacts
WHEN (1:1) = 'K'
( rec_type    POSITION(1:1)    FILLER,
  contact_id  POSITION(2:9)    INTEGER EXTERNAL,
  customer_id POSITION(10:17)  INTEGER EXTERNAL,
  name        POSITION(18:67)  CHAR,
  phone       POSITION(68:87)  CHAR )

INTO TABLE crm.addresses
WHEN (1:1) = 'A'
( rec_type    POSITION(1:1)   FILLER,
  address_id  POSITION(2:9)   INTEGER EXTERNAL,
  customer_id POSITION(10:17) INTEGER EXTERNAL,
  street      POSITION(18:77) CHAR,
  city        POSITION(78:107) CHAR,
  postcode    POSITION(108:117) CHAR )

Rewinding to the start of a record

In fixed-width multi-table loads, each subsequent INTO TABLE block normally picks up where the previous field mapping left off. To rewind to the start of the physical record for the next block, use POSITION(1) as the first field:

INTO TABLE crm.contacts
WHEN (1:1) = 'K'
( anchor     POSITION(1)   FILLER,      -- rewind to byte 1
  contact_id POSITION(2:9) INTEGER EXTERNAL,
  ... )

For delimited data, this rewinding happens automatically — each INTO TABLE block re-parses the record from the beginning.

Header and trailer records

Some feeds include a header row (column names or file metadata) and a footer row (record count, checksum). Skip or route them explicitly:

LOAD DATA
INFILE 'daily_feed.csv'
APPEND

INTO TABLE etl.feed_header
WHEN (1:3) = 'HDR'
( raw_header CHAR(500) )

INTO TABLE sales.transactions
WHEN (1:3) = 'TXN'
FIELDS TERMINATED BY ','
( rec_type FILLER, txn_id INTEGER EXTERNAL, amount DECIMAL EXTERNAL,
  txn_date DATE "YYYY-MM-DD" )

INTO TABLE etl.feed_trailer
WHEN (1:3) = 'TRL'
( raw_trailer CHAR(500) )

Or use SKIP=1 on the command line to skip the first record entirely if it's a pure column header.

Commit and error handling in multi-table loads

• ROWS=n commits every n rows loaded per table, not per-file row. A single file record that loads into two tables counts as one row each.
• If a record matches a WHEN condition but fails format conversion for one table, it is written to the bad file; the load continues for other tables.
• ERRORS=n counts the total rejected rows across all tables combined.

Example — normalising a flat employee export

-- flat_employees.ctl
OPTIONS ( ERRORS=100, ROWS=5000 )
LOAD DATA
INFILE 'flat_employees.csv'
APPEND

INTO TABLE hr.employees
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( employee_id,
  first_name  CHAR(20),
  last_name   CHAR(25),
  email       CHAR(100),
  hire_date   DATE "YYYY-MM-DD",
  -- skip department and location fields
  dept_id     FILLER,
  dept_name   FILLER,
  location_id FILLER,
  city        FILLER )

INTO TABLE hr.departments
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( employee_id   FILLER,
  first_name    FILLER,
  last_name     FILLER,
  email         FILLER,
  hire_date     FILLER,
  dept_id       INTEGER EXTERNAL,
  dept_name     CHAR(60),
  location_id   INTEGER EXTERNAL,
  city          FILLER )

Note: loading into hr.departments this way would insert duplicate rows since every employee in the same department produces a separate departments insert. Pre-process the file or use SELECT DISTINCT after loading into a staging table to handle this.

Best practices

• Always include WHEN on every INTO TABLE block unless you genuinely want every record going to multiple tables
• Use FILLER liberally — mark fields that don't belong to a given table so they don't accidentally get loaded
• For large multi-table loads, verify the row counts in the log for each target table separately
• If the load order matters (parent before child), consider separate sequential sqlldr runs rather than a single multi-table control file
• Check the discard file after every run — unmatched records quietly end up there with no error message