# Deep Dive — INSERT SELECT

> The database copies rows faster than any loop can: one statement, one pass, and with `APPEND` it never even touches the buffer cache.

Used in: [Chapter 1 — SQL First](../course-plan.md), [Chapter 9 — Huge Tables](../course-plan.md) · Appendix entry: [INSERT SELECT](../appendix.md#insert-select) · Related: [APPEND HINT](append-hint.md), [PARALLEL DML](parallel-dml.md), [MERGE](merge.md), [EXCHANGE PARTITION](exchange-partition.md)

---

## 1. What It Is and When

`INSERT ... SELECT` inserts the result of a query into a table. Anything you would do with a cursor loop that only inserts — archiving, copying, transforming, splitting into several tables — is one of these statements.

Variants:
- `INSERT INTO t SELECT ...` — single target.
- `INSERT ALL INTO t1 ... INTO t2 ... SELECT ...` — every row goes to every target (multi-table insert).
- `INSERT FIRST WHEN c1 THEN INTO t1 WHEN c2 THEN INTO t2 ELSE INTO t3 SELECT ...` — each row goes to the first matching target.
- `CREATE TABLE t AS SELECT ...` (CTAS) — when the target does not exist yet; direct-path by default, `NOLOGGING` optional.

## 2. Syntax

```sql
INSERT [hint] INTO <table> [(<col>, ...)]
SELECT ... FROM ... [WHERE ...]
[LOG ERRORS [INTO <err_table>] [(<tag>)] [REJECT LIMIT { <n> | UNLIMITED }]];

-- multi-table
INSERT [ALL | FIRST]
  [WHEN <cond> THEN] INTO <t1> [(<cols>)] VALUES (<exprs>)
  [WHEN <cond> THEN] INTO <t2> [(<cols>)] VALUES (<exprs>)
  [ELSE INTO <t3> ...]
SELECT ... FROM ...;

-- new table
CREATE TABLE <t> [NOLOGGING] [PARALLEL n] AS SELECT ... ;
```

`RETURNING INTO` is **not** available with `INSERT ... SELECT` — only with single-row `INSERT ... VALUES`.

## 3. Examples on the Course Schema

### 3a. Archive — always list the columns

```sql
INSERT INTO orders_archive (order_id, customer_id, order_date, amount, status)
SELECT order_id, customer_id, order_date, amount, status
FROM   orders
WHERE  order_date < DATE '2025-01-01';
```

`INSERT INTO orders_archive SELECT * FROM orders` works until someone adds a column to one table and not the other. Listing columns turns that into a compile error instead of shifted data.

### 3b. Direct path + parallel for the big one

```sql
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ APPEND PARALLEL(a, 8) */ INTO orders_archive a
  (order_id, customer_id, order_date, amount, status)
SELECT /*+ PARALLEL(o, 8) */ order_id, customer_id, order_date, amount, status
FROM   orders o
WHERE  order_date < DATE '2025-01-01';
COMMIT;      -- required before reading orders_archive in this session (ORA-12838)
```

See [APPEND HINT](append-hint.md) and [PARALLEL DML](parallel-dml.md) for the mechanics and restrictions.

### 3c. Split one scan into two tables — INSERT FIRST

```sql
INSERT FIRST
  WHEN status = 'CANCELLED' THEN
    INTO orders_cancelled (order_id, customer_id, order_date, amount)
    VALUES (order_id, customer_id, order_date, amount)
  WHEN order_date < DATE '2025-01-01' THEN
    INTO orders_archive (order_id, customer_id, order_date, amount, status)
    VALUES (order_id, customer_id, order_date, amount, status)
SELECT order_id, customer_id, order_date, amount, status
FROM   orders
WHERE  status = 'CANCELLED' OR order_date < DATE '2025-01-01';
```

One scan of `orders`, two targets. The loop version would scan once and insert row by row into two tables; the two-statement version would scan twice.

### 3d. Transform on the way in — sequences and expressions

```sql
INSERT INTO order_summary (summary_id, customer_id, order_month, total_amount, order_count)
SELECT order_summary_seq.NEXTVAL,
       customer_id,
       TRUNC(order_date, 'MM'),
       SUM(amount),
       COUNT(*)
FROM   orders
WHERE  order_date >= DATE '2025-01-01'
GROUP  BY customer_id, TRUNC(order_date, 'MM');
```

`NEXTVAL` in the select list is allowed (not inside `DISTINCT`, `GROUP BY`, `ORDER BY` or subqueries — wrap the aggregate in an inline view if needed).

### 3e. New table from a query — CTAS

```sql
CREATE TABLE orders_2024 NOLOGGING PARALLEL 8 AS
SELECT /*+ PARALLEL(o, 8) */ *
FROM   orders o
WHERE  order_date >= DATE '2024-01-01' AND order_date < DATE '2025-01-01';
```

Direct-path, no undo for the rows, minimal redo with `NOLOGGING`. Back it up afterwards.

### 3f. Do not stop on bad rows — LOG ERRORS

```sql
EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('ORDERS_ARCHIVE');

INSERT INTO orders_archive (order_id, customer_id, order_date, amount, status)
SELECT order_id, customer_id, order_date, amount, status FROM orders_stage
LOG ERRORS INTO err$_orders_archive ('stage load') REJECT LIMIT UNLIMITED;
```

## 4. What Breaks at Scale

- **Conventional insert of 40M rows** goes through the buffer cache block by block, generates full undo and redo, and maintains every index row by row. Direct path (`APPEND`) skips the cache and the undo; `NOLOGGING` skips most redo.
- **Indexes.** For a load that adds a large fraction of the table, it is usually faster to mark indexes `UNUSABLE`, load, then `REBUILD ... NOLOGGING PARALLEL`. Direct path maintains indexes in bulk at the end anyway, but a rebuild of a big index is cheaper than a bulk merge into it.
- **One transaction.** A 40M-row conventional insert is one transaction: undo sizing, and a failure at row 39M rolls back everything. Direct path needs almost no undo. Chunking (partition by partition, or [DBMS_PARALLEL_EXECUTE](dbms_parallel_execute.md)) gives restartability.
- **Triggers** fire per row for conventional and direct-path alike — and an enabled trigger silently turns `APPEND` into a conventional insert.

## 5. Pitfalls

1. `INSERT INTO t SELECT *` without a column list — positional mapping breaks the day a column is added.
2. `ORA-12838` — reading a table in the same session after a direct-path insert, before `COMMIT`.
3. `NOLOGGING` on a database in `FORCE LOGGING` mode (every Data Guard setup) does nothing — and where it does work, the table is unrecoverable until the next backup.
4. `NEXTVAL` inside `GROUP BY`/`DISTINCT` queries: `ORA-02287`. Aggregate in an inline view, `NEXTVAL` outside.
5. `INSERT ALL` with `WHEN` clauses evaluates every `WHEN` for every row; `INSERT FIRST` stops at the first match. Choose deliberately.
6. Sequence gaps and `CACHE`: a 40M-row insert with `CACHE 20` hits the sequence 2M times. `ALTER SEQUENCE ... CACHE 10000` for bulk loads.
7. A `SELECT` with `ORDER BY` in an `INSERT ... SELECT` sorts for nothing — unless you are deliberately clustering rows for an index (`ORDER BY` the index columns can help compression and clustering factor).

## 6. Related

- [APPEND HINT](append-hint.md) — direct path and its restrictions.
- [PARALLEL DML](parallel-dml.md) — using all the cores.
- [MERGE](merge.md) — when some rows already exist.
- [EXCHANGE PARTITION](exchange-partition.md) — when the "copy" can be a metadata swap instead.