INSERT, APPEND, REPLACE, TRUNCATE

The load mode keyword controls what happens to the target table before rows are loaded. It appears either at the top level (applies to all INTO TABLE blocks) or inside each INTO TABLE block for per-table control.

The four modes

Mode	Pre-load action	Fails if	Use when
INSERT	None — table must be empty	Table contains rows	Initial one-time load; error guards against accidental re-run
APPEND	None — rows are added	Never	Incremental loads, daily feeds, parallel loads
REPLACE	DELETE FROM table	Active locks exist	Full refresh; triggers fire on delete; FK children may block
TRUNCATE	TRUNCATE TABLE	FK references exist (usually)	Fastest full refresh; does not fire row triggers; reclaims HWM

INSERT (default)

LOAD DATA
INFILE 'regions.csv'
INSERT                           -- fails if table has any rows
INTO TABLE hr.regions
FIELDS TERMINATED BY ','
( region_id, region_name )

If hr.regions already has rows you get:

SQL*Loader-601: For INSERT option, table must be empty. Error on table HR.REGIONS

Use INSERT when the load is the table's initial population and you want an error if someone accidentally ran the load twice.

APPEND

LOAD DATA
INFILE 'orders_jan.csv'
APPEND                           -- adds to whatever is there
INTO TABLE sales.orders
FIELDS TERMINATED BY ','
( order_id, order_date DATE "YYYY-MM-DD", amount DECIMAL EXTERNAL )

APPEND with DIRECT=TRUE writes above the high-water mark — the fastest path for large loads. APPEND with conventional path inserts through the SQL layer; Oracle may recycle deleted blocks below the HWM.

REPLACE

LOAD DATA
INFILE 'full_catalog.csv'
REPLACE                          -- issues DELETE FROM catalog before loading
INTO TABLE products.catalog
FIELDS TERMINATED BY ','
( product_id, name CHAR(200), price DECIMAL EXTERNAL )

• Fires ON DELETE triggers and BEFORE DELETE triggers for each row
• Row-by-row delete generates undo and redo — very slow on large tables
• Foreign keys referencing catalog will block the delete unless they are disabled
• Slower than TRUNCATE but preserves FK integrity enforcement

TRUNCATE

LOAD DATA
INFILE 'full_dim_product.csv'
TRUNCATE                         -- issues TRUNCATE TABLE dim_product before loading
INTO TABLE dw.dim_product
FIELDS TERMINATED BY ','
( product_id, name CHAR(200), category CHAR(60), list_price DECIMAL EXTERNAL )

• Much faster than REPLACE — deallocates extents rather than generating undo per row
• Does not fire row-level DELETE triggers
• Resets the high-water mark (important for subsequent direct path efficiency)
• Fails if another table has an enabled FK referencing dim_product

Handling FK children with TRUNCATE

-- Option 1: disable the FK, truncate, re-enable
ALTER TABLE dw.fact_sales DISABLE CONSTRAINT fk_product_id;
-- run sqlldr with TRUNCATE ...
ALTER TABLE dw.fact_sales ENABLE CONSTRAINT fk_product_id;

-- Option 2: delete from child first, then sqlldr TRUNCATE the parent
-- Option 3: use REPLACE instead (slower but no FK wrestling)

Per-table load mode

A single control file can use different modes per table:

LOAD DATA
INFILE 'mixed.csv'

INTO TABLE dw.dim_product
TRUNCATE
WHEN rec_type = 'P'
( rec_type POSITION(1:1) FILLER,
  product_id POSITION(2:7) INTEGER EXTERNAL,
  name       POSITION(8:57) CHAR )

INTO TABLE dw.dim_customer
APPEND
WHEN rec_type = 'C'
( rec_type   POSITION(1:1) FILLER,
  customer_id POSITION(2:7) INTEGER EXTERNAL,
  name        POSITION(8:57) CHAR )

Here dim_product is truncated before loading while dim_customer is appended to — two tables, two modes, one pass over the data file.

Interaction with direct path

Load mode	Direct path behaviour
INSERT	Writes above HWM; errors if table has rows
APPEND	Writes above current HWM — standard direct path
REPLACE	Deletes rows conventionally first, then writes above HWM
TRUNCATE	Truncates first (resets HWM to 0), then writes from bottom

TRUNCATE + DIRECT=TRUE is the canonical pattern for full-refresh dimension loads: reset the table, write all blocks from scratch, rebuild indexes once.

Best practices

• Default to APPEND for incremental feeds and TRUNCATE for full-refresh loads
• Never use REPLACE on tables larger than a few hundred thousand rows — the row-by-row delete is too slow; use TRUNCATE instead
• Pair INSERT with a pre-check script that asserts COUNT(*) = 0 before handing off to sqlldr
• When using TRUNCATE in a load pipeline, script the FK disable/enable around the sqlldr call
• Log the load mode in your runbook — accidental REPLACE on a fact table with 500 M rows is a very bad day