Oracle SQL INSERT … SELECT: Practical Techniques in 19c

Introduction

When you need to move or transform a large volume of data in one go, insert select is a go-to technique. This article walks through the correct usage of select insert, performance tuning, and precautions for locks and logging—with “run-as-is” examples on Oracle Database 19c plus plain-English explanations of what each SQL does.

See here for information on creating a table.

Basics: Syntax and Mental Model (start here)

INSERT ... SELECT inserts the result of a SELECT directly into another table. The key is matching the number of columns, order, and data types.

-- Basic syntax (explicit column list is safer)
INSERT INTO target_table (colA, colB, colC, ...)
SELECT exprA, exprB, exprC, ...
  FROM source_table
 WHERE condition;

What the SQL means (in simple terms)

INSERT INTO target_table (cols...) — First, declare which table and which columns to write to.
SELECT expr... FROM source_table — Build the input using a SELECT (raw columns, calculations, functions all OK).
WHERE condition — Filter which rows to take.
Tip: If you omit a column in the INSERT list, its DEFAULT applies (or NULL if no default). You can also insert NULL explicitly.

Text diagram: Flow of INSERT … SELECT (concept)

┌──────────── SELECT side (prepare data) ────────────┐
│ Read source tables/views; apply calc/transform/aggregation as needed │
└───────────────┬───────────────────┘
                    │ resulting row set (output)
┌───────────────▼───────────────────┐
│           INSERT target table (destination)           │
│ Constraint checks → index updates → UNDO/REDO (log) generation │
└────────────────────────────────┘

Preparation First (create user, grant privileges, define tables)

In a CDB, switch to the target PDB before you start.

-- (SYSDBA) Create a user and grant privileges
CREATE USER appuser IDENTIFIED BY "Str0ng#Pass"
  DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE TO appuser;

What the SQL means

CREATE USER — Create a login/schema.
DEFAULT TABLESPACE — The tablespace the user uses by default.
QUOTA UNLIMITED — No quota limit on tablespace users.
GRANT ... — Allow login (CREATE SESSION), table creation, and sequence creation.

-- (APPUSER) Source and target tables
CREATE TABLE emp_src (
  id        NUMBER        PRIMARY KEY,
  name      VARCHAR2(50)  NOT NULL,
  dept_id   NUMBER,
  sal       NUMBER(10,2),
  hired_on  DATE          DEFAULT SYSDATE
);

-- Sample data
INSERT INTO emp_src VALUES (1,'Sato',   10, 350000, TO_DATE('2024-04-01','YYYY-MM-DD'));
INSERT INTO emp_src VALUES (2,'Suzuki', 20, 420000, SYSDATE);
INSERT INTO emp_src VALUES (3,'Tanaka', 10, 500000, SYSDATE);
COMMIT;

-- Target table (ID by IDENTITY: “reassign new IDs” design)
CREATE TABLE emp_tgt (
  id        NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name      VARCHAR2(50) NOT NULL,
  dept_id   NUMBER,
  sal       NUMBER(10,2),
  hired_on  DATE DEFAULT SYSDATE,
  status    CHAR(1) DEFAULT 'A' CHECK (status IN ('A','I'))
);

What the SQL means

PRIMARY KEY — Unique + NOT NULL constraint for the primary key.
DEFAULT SYSDATE — Auto-populate the insert time.
IDENTITY — Auto-number on the target side (no need to supply the ID).
CHECK (status IN ('A','I')) — Only A or I is allowed.

Common Patterns and How to Write Them (with “what it means”)

1) Straight copy (column order matches)

INSERT INTO emp_tgt (name, dept_id, sal, hired_on)
SELECT name, dept_id, sal, hired_on
  FROM emp_src;
COMMIT;

Command execution example

SQL> col name for a10
SQL> SELECT * FROM emp_src;

        ID NAME          DEPT_ID        SAL HIRED_ON
---------- ---------- ---------- ---------- ---------
         1 Sato               10     350000 01-APR-24
         2 Suzuki             20     420000 23-AUG-25
         3 Tanaka             10     500000 23-AUG-25

SQL> SELECT * FROM emp_tgt;

no rows selected

SQL> INSERT INTO emp_tgt (name, sal, dept_id, hired_on)
  2  SELECT name, sal, dept_id, hired_on
  3    FROM emp_src
  4   WHERE dept_id IS NOT NULL;

3 rows created.

SQL> SELECT * FROM emp_tgt;

        ID NAME          DEPT_ID        SAL HIRED_ON  S
---------- ---------- ---------- ---------- --------- -
         1 Sato               10     350000 01-APR-24 A
         2 Suzuki             20     420000 23-AUG-25 A
         3 Tanaka             10     500000 23-AUG-25 A

What the SQL means

SELECT ... FROM emp_src — Take source columns as they are.
Target uses IDENTITY, so omit id (DB auto-numbers it).
COMMIT — Make the change permanent.

2) Reorder columns / resolve name differences

INSERT INTO emp_tgt (name, sal, dept_id, hired_on)
SELECT name, sal, dept_id, hired_on
  FROM emp_src
 WHERE dept_id IS NOT NULL;

What the SQL means

Align the SELECT output order with the target column list.
WHERE dept_id IS NOT NULL — Prevent invalid data from slipping in.

3) Mix constants, expressions, and functions (transform on load)

INSERT INTO emp_tgt (name, dept_id, sal, hired_on, status)
SELECT name,
       dept_id,
       ROUND(sal * 1.05, 2),               -- 5% uplift, keep 2 decimals
       NVL(hired_on, TRUNC(SYSDATE)),      -- default to today when null
       'A'                                  -- set a constant during load
  FROM emp_src
 WHERE sal >= 400000;

Command execution example

SQL> INSERT INTO emp_tgt (name, dept_id, sal, hired_on, status)
  2  SELECT name,
  3         dept_id,
  4         ROUND(sal * 1.05, 2),
  5         NVL(hired_on, TRUNC(SYSDATE)),
  6         'A'
  7    FROM emp_src
  8   WHERE sal >= 400000;

2 rows created.

SQL> SELECT * FROM emp_tgt;

        ID NAME          DEPT_ID        SAL HIRED_ON  S
---------- ---------- ---------- ---------- --------- -
         1 Sato               10     350000 01-APR-24 A
         2 Suzuki             20     420000 23-AUG-25 A
         3 Tanaka             10     500000 23-AUG-25 A
         4 Suzuki             20     441000 23-AUG-25 A  ★
         5 Tanaka             10     525000 23-AUG-25 A  ★

What the SQL means

Use ROUND / NVL to apply on-the-fly transformations.
Insert a constant (status='A').
Filter with WHERE sal >= ....

4) Load unique rows only (DISTINCT)

INSERT INTO emp_tgt (name, dept_id, sal, hired_on)
SELECT DISTINCT name, dept_id, sal, hired_on
  FROM emp_src;

What the SQL means

DISTINCT — Insert only one copy of identical rows.

5) Aggregate into a summary table

CREATE TABLE sales_summary (
  dept_id NUMBER,
  y       NUMBER,
  m       NUMBER,
  total_amt NUMBER
);

INSERT INTO sales_summary (dept_id, y, m, total_amt)
SELECT dept_id,
       EXTRACT(YEAR  FROM hired_on),
       EXTRACT(MONTH FROM hired_on),
       SUM(sal)
  FROM emp_src
 GROUP BY dept_id,
          EXTRACT(YEAR  FROM hired_on),
          EXTRACT(MONTH FROM hired_on);

What the SQL means

EXTRACT(YEAR/MONTH ...) — Pull out year/month.
SUM + GROUP BY — Compute totals per dept × year-month, then insert.

Working with ID Assignment (IDENTITY / SEQUENCE)

Target uses IDENTITY

GENERATED ALWAYS — DB always assigns the ID (supplying it causes an error).
BY DEFAULT — DB assigns by default; you may supply a value if needed.

-- Have the DB auto-assign (omit the ID column)
INSERT INTO emp_tgt (name, dept_id, sal, hired_on)
SELECT name, dept_id, sal, hired_on FROM emp_src;

-- Preserve existing IDs (allowed only with BY DEFAULT; watch for duplicates)
INSERT INTO emp_tgt (id, name, dept_id, sal, hired_on)
SELECT id, name, dept_id, sal, hired_on FROM emp_src;

Command execution example

SQL> INSERT INTO emp_tgt (name, dept_id, sal, hired_on)
  2  SELECT name, dept_id, sal, hired_on FROM emp_src;

3 rows created.

SQL> SELECT * FROM emp_tgt order by id;

        ID NAME          DEPT_ID        SAL HIRED_ON  S
---------- ---------- ---------- ---------- --------- -
         1 Sato               10     350000 01-APR-24 A
         2 Suzuki             20     420000 23-AUG-25 A
         3 Tanaka             10     500000 23-AUG-25 A
         4 Suzuki             20     441000 23-AUG-25 A
         5 Tanaka             10     525000 23-AUG-25 A
         6 Sato               10     350000 01-APR-24 A
         7 Suzuki             20     420000 23-AUG-25 A
         8 Tanaka             10     500000 23-AUG-25 A
         9 Sato               10     350000 01-APR-24 A
        10 Suzuki             20     420000 23-AUG-25 A
        11 Tanaka             10     500000 23-AUG-25 A

11 rows selected.

SQL> INSERT INTO emp_tgt (id, name, dept_id, sal, hired_on)
  2  SELECT id, name, dept_id, sal, hired_on FROM emp_src;
INSERT INTO emp_tgt (id, name, dept_id, sal, hired_on)
*
ERROR at line 1:
ORA-00001: unique constraint (APPUSER.SYS_C007542) violated  ★

What the SQL means

First case: rely on auto-numbering; ignore source id.
Second case: carry over IDs (mind uniqueness and constraints).

Target uses SEQUENCE

CREATE SEQUENCE emp_seq START WITH 1000 INCREMENT BY 1;

INSERT INTO emp_tgt (id, name, dept_id, sal, hired_on)
SELECT emp_seq.NEXTVAL, name, dept_id, sal, hired_on
  FROM emp_src;

What the SQL means

NEXTVAL — Issue the next number for id.
Adjust START WITH to avoid clashes with existing ranges.

Cautions for select insert (performance, locks, logging)

Conventional path vs. direct path (APPEND)

-- For bulk loads (write above the HWM in big chunks)
INSERT /*+ APPEND */ INTO emp_tgt (name, dept_id, sal, hired_on)
SELECT name, dept_id, sal, hired_on FROM emp_src WHERE dept_id IS NOT NULL;
COMMIT;

What the SQL means & what to watch

/*+ APPEND */ — Use direct-path insert (new extents above the HWM).
Can require strong locks on the target and compete with other transactions.
Since it doesn’t compact existing blocks, the table can grow larger afterward.

Parallel DML

ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ APPEND PARALLEL(emp_tgt,4) */ INTO emp_tgt (name, dept_id, sal, hired_on)
SELECT /*+ PARALLEL(emp_src,4) */ name, dept_id, sal, hired_on
  FROM emp_src;
COMMIT;

What the SQL means & what to watch

ENABLE PARALLEL DML — Allow parallel inserts.
PARALLEL(emp_tgt,4) — Set degrees for target/source.
Be mindful of transaction boundaries; reading the same table within the same transaction can have restrictions.

When to minimize logging (NOLOGGING)

ALTER TABLE emp_tgt NOLOGGING;      -- REDO縮小（完全に0ではない）
INSERT /*+ APPEND */ INTO emp_tgt (name, dept_id, sal, hired_on)
SELECT name, dept_id, sal, hired_on FROM emp_src;
ALTER TABLE emp_tgt LOGGING;        -- 終了後に戻す

What the SQL means & what to watch

NOLOGGING — Reduce REDO to speed up (not zero). Risk: parts may be unrecoverable after media failure.
Limit the scope and plan backups before/after.

Validate the Load (sanity checks and sampling)

-- Do counts match? (quick sanity check)
SELECT COUNT(*) FROM emp_src;
SELECT COUNT(*) FROM emp_tgt;

-- Sample view (does the content look right?)
SELECT id, name, dept_id, sal, hired_on, status
  FROM emp_tgt
 ORDER BY id
 FETCH FIRST 10 ROWS ONLY;

What the SQL means

Matching counts are the baseline.
Spot-check using a sample (FETCH FIRST).
For deeper checks, compare MIN/MAX/AVG or per-dept counts.

Common Errors and Fixes (lookup)

ORA-00913: too many values
→ Too many SELECT columns. Align the number with the INSERT column list.

ORA-00947: not enough values
→ SELECT has too few columns. Align the number with the INSERT column list.

ORA-00001: unique constraint violated
→ Duplicate key. Deduplicate, revisit ID assignment, and pre-check for conflicts.

ORA-02291: integrity constraint violated - parent key not found
→ Missing parent for a foreign key. Adjust load order or referential design.

ORA-01536: space quota exceeded for tablespace
→ Quota exceeded. Fix tablespace quota or free up disk space.

Summary

With INSERT … SELECT, match column count, types, and order—and state column names explicitly.
Choose IDENTITY vs. SEQUENCE based on requirements, and design to avoid duplicates/constraint violations.
For large loads, apply APPEND / Parallel DML / NOLOGGING judiciously and understand the trade-off between speed, locks, and recoverability.
After loading, validate counts and samples; consider stats refresh or index maintenance if needed.

This article targets Oracle Database 19c (other versions may differ in defaults and behavior).

[参考]
INSERT