LOAD DATA, INFILE & INTO TABLE

These three keywords sit at the spine of every control file. LOAD DATA opens the load. INFILE names the data source. INTO TABLE names the destination and field mapping.

Inline data with INFILE *

* means "data is right here in the control file, after BEGINDATA":

LOAD DATA
INFILE *
INTO TABLE hr.regions
FIELDS TERMINATED BY ','
( region_id, region_name )
BEGINDATA
1,Europe
2,Americas
3,Asia
4,Middle East and Africa

Useful for tiny seed loads and tutorials. Don't do this with millions of rows.

External data files

INFILE 'employees.dat'                 -- relative to sqlldr's CWD
INFILE '/data/load/employees.dat'      -- absolute
INFILE 'C:\loads\employees.dat'        -- Windows

Multiple INFILEs are allowed — sqlldr reads them in order:

LOAD DATA
INFILE 'jan.csv'
INFILE 'feb.csv'
INFILE 'mar.csv'
INTO TABLE hr.sales
APPEND
FIELDS TERMINATED BY ','
( sale_date DATE "YYYY-MM-DD", region, amount )

File-format specifiers on INFILE

You can describe how records are framed inside an INFILE:

INFILE 'fixed.dat'  "FIX 80"            -- 80-byte fixed records
INFILE 'var.dat'    "VAR 4"             -- first 4 chars give record length
INFILE 'stream.dat' "STR x'0a'"         -- LF-terminated stream (default in *nix)
INFILE 'stream.dat' "STR x'0d0a'"       -- CRLF-terminated stream (Windows)

STR accepts any byte string — useful for delimited records with non-newline terminators (e.g. STR '|*|').

INTO TABLE basics

INTO TABLE hr.employees
APPEND                          -- load mode (or use top-level INSERT/REPLACE/TRUNCATE)
WHEN region_id = '01'           -- optional row filter
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  employee_id,
  first_name CHAR(20),
  last_name  CHAR(25),
  hire_date  DATE "YYYY-MM-DD"
)

Schema-qualify the table when loading as a different user than the owner: INTO TABLE hr.employees.

Multiple INTO TABLE blocks

A single control file can split one input stream across multiple tables. Use WHEN to route, and POSITION(1) at the top of each subsequent block to rewind to the start of each record:

LOAD DATA
INFILE 'mixed.dat'
APPEND

INTO TABLE hr.customers
WHEN rec_type = 'C'
( rec_type   POSITION(1:1)  CHAR,
  customer_id POSITION(2:7) INTEGER EXTERNAL,
  name       POSITION(8:50) CHAR )

INTO TABLE hr.orders
WHEN rec_type = 'O'
( rec_type   POSITION(1:1)   CHAR,
  order_id   POSITION(2:7)   INTEGER EXTERNAL,
  amount     POSITION(8:20)  DECIMAL EXTERNAL )

PRESERVE BLANKS

By default, leading/trailing whitespace is trimmed from delimited fields. PRESERVE BLANKS keeps every byte:

LOAD DATA
INFILE 'codes.dat'
PRESERVE BLANKS
INTO TABLE codes
FIELDS TERMINATED BY ','
( code CHAR(10), description CHAR(100) )

Critical for fixed-format codes where leading spaces are significant (e.g. 042 ≠ 42).

Best practices

• Use absolute paths for INFILE once you move past local testing
• Quote file paths if they contain spaces: INFILE '/data/jan loads/jan.csv'
• Multiple INFILEs share the same INTO TABLE definition — use it for time-partitioned files
• INFILE * is fine for ≤ 100 rows; everything else lives in a separate .dat
• Always specify WHEN on every INTO TABLE in a multi-table load — otherwise rows get duplicated across tables