Inserting Data

With tables, constraints, and sequences all in place, the next step is loading data. Oracle's INSERT statement has several forms — single-row inserts for transactional use, multi-row inserts for loading several rows at once, and INSERT INTO SELECT for deriving new rows from existing data. This lesson covers all three, along with how DEFAULT values behave on insert, what happens when constraints are violated, and how to use RETURNING INTO to capture generated values without a follow-up query.

To follow along with the examples in this lesson, use the course dataset from Lesson 8.

Single-Row INSERT

The standard INSERT statement adds one row to a table. The column list and the values list must match in count and order — Oracle maps the first value to the first named column, the second to the second, and so on. Omitting the column list forces you to supply a value for every column in creation order, which makes statements brittle and hard to read. Always name the columns explicitly.

Columns with a DEFAULT defined can be omitted from the column list entirely — Oracle fills in the default automatically. A column can also be included in the list with the keyword DEFAULT as its value to trigger the default explicitly, which is useful when the default needs to be applied conditionally.

-- Full column list — every column named explicitly
INSERT INTO customers (customer_id, full_name, email, phone, country, city, loyalty_tier, created_date)
VALUES (seq_customer_id.NEXTVAL, 'Marcus Webb', 'marcus@example.com', '+44 7700 900456', 'UK', 'London', 'gold', SYSDATE);

-- Omitting columns that have DEFAULT values
-- loyalty_tier defaults to 'standard', created_date defaults to SYSDATE
INSERT INTO customers (customer_id, full_name, email, country)
VALUES (seq_customer_id.NEXTVAL, 'Priya Sharma', 'priya@example.com', 'India');

-- Using the DEFAULT keyword explicitly inside VALUES
INSERT INTO customers (customer_id, full_name, email, country, loyalty_tier, created_date)
VALUES (seq_customer_id.NEXTVAL, 'James Okafor', 'james@example.com', 'Nigeria', DEFAULT, DEFAULT);

-- Confirm all three rows were inserted
SELECT customer_id, full_name, loyalty_tier, created_date
FROM   customers
WHERE  email IN ('marcus@example.com', 'priya@example.com', 'james@example.com')
ORDER BY customer_id;

• Omitting a column from the INSERT list is equivalent to inserting DEFAULT — if no default is defined and the column is NOT NULL, Oracle raises ORA-01400
• Always include the column list even when inserting every column — if a column is later added to the table, a columnless INSERT will fail or silently map values to the wrong columns
• SYSDATE in a VALUES clause captures the exact moment the INSERT executes — each row in a multi-statement batch gets the time that specific statement ran, not a single shared timestamp

Inserting into Related Tables

When tables are linked by foreign keys the parent row must exist before the child row. The typical pattern is to insert the parent using NEXTVAL, then immediately insert the child using CURRVAL to reference the ID just generated — no separate query needed to retrieve it.

-- Insert a new order for an existing customer
INSERT INTO orders (order_id, customer_id, order_date, status, total_amount)
VALUES (seq_order_id.NEXTVAL, 13, SYSDATE, 'pending', 0);

-- Insert two order items referencing the order just created
-- CURRVAL on seq_order_id returns the order_id used in the INSERT above
INSERT INTO order_items (order_item_id, order_id, product_id, quantity, unit_price)
VALUES (seq_order_item_id.NEXTVAL, seq_order_id.CURRVAL, 3, 1, 149.99);

INSERT INTO order_items (order_item_id, order_id, product_id, quantity, unit_price)
VALUES (seq_order_item_id.NEXTVAL, seq_order_id.CURRVAL, 7, 2, 49.99);

-- Insert a payment for the same order
INSERT INTO payments (payment_id, order_id, payment_date, amount, method)
VALUES (seq_payment_id.NEXTVAL, seq_order_id.CURRVAL, SYSDATE, 249.97, 'credit_card');

-- Verify the full order
SELECT o.order_id,
       oi.product_id,
       oi.quantity,
       oi.unit_price,
       p.amount        AS payment_amount,
       p.method
FROM   orders      o
JOIN   order_items oi ON oi.order_id   = o.order_id
JOIN   payments    p  ON p.order_id    = o.order_id
WHERE  o.customer_id = 13;

• CURRVAL is safe to use across multiple INSERT statements in the same session — it holds the same value until NEXTVAL is called again on the same sequence
• Inserting the child row before the parent raises ORA-02291 — always insert in parent-first order when foreign key constraints are enabled
• The total_amount on the order was set to 0 at insert time — in a real application a trigger or application layer would update this after all items are added; for now it reflects the insert state

Multi-Row INSERT and INSERT ALL

Oracle does not support the comma-separated multi-row VALUES syntax used in some other databases. Instead, Oracle provides two approaches: INSERT ALL for inserting multiple rows into one or more tables in a single statement, and INSERT INTO ... SELECT for deriving rows from a query.

INSERT ALL lists multiple INTO clauses followed by a mandatory SELECT — even when no table is being selected from. The dummy SELECT 1 FROM DUAL at the end satisfies the syntax requirement.

-- INSERT ALL inserts multiple rows in a single statement
-- The SELECT at the end is mandatory syntax — DUAL provides the single row needed
INSERT ALL
    INTO products (product_id, product_name, category, unit_price, stock_qty, active_flag)
    VALUES (seq_product_id.NEXTVAL, 'Mechanical Keyboard', 'Electronics', 89.99, 75, 'Y')
    INTO products (product_id, product_name, category, unit_price, stock_qty, active_flag)
    VALUES (seq_product_id.NEXTVAL, 'USB-C Hub 7-Port', 'Electronics', 39.99, 120, 'Y')
    INTO products (product_id, product_name, category, unit_price, stock_qty, active_flag)
    VALUES (seq_product_id.NEXTVAL, 'Laptop Stand Aluminium', 'Accessories', 59.99, 90, 'Y')
SELECT 1 FROM DUAL;

-- INSERT ALL into multiple different tables in one statement
-- Useful for audit tables or staging tables that must mirror a transactional insert
INSERT ALL
    INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, department_id)
    VALUES (seq_employee_id.NEXTVAL, 'Fatima', 'Al-Hassan', 'fatima@dataplexa.com', DATE '2024-03-01', 'IT_PROG', 20)
    INTO salaries (salary_id, employee_id, amount, effective_date, salary_type)
    VALUES (seq_salary_id.NEXTVAL, seq_employee_id.CURRVAL, 5800.00, DATE '2024-03-01', 'monthly')
SELECT 1 FROM DUAL;

SELECT employee_id, first_name, last_name FROM employees WHERE email = 'fatima@dataplexa.com';

• INSERT ALL does not support RETURNING INTO — if you need to capture generated IDs across multiple rows, use individual INSERT statements or a FORALL loop in PL/SQL
• Each INTO clause in INSERT ALL is evaluated independently — NEXTVAL is called once per INTO clause, so each row gets a distinct sequence value
• Oracle's INSERT ALL also supports a conditional form — INSERT FIRST and WHEN condition THEN INTO — for routing rows from a SELECT into different target tables based on column values

INSERT INTO SELECT

INSERT INTO SELECT derives new rows from a query rather than from literal values. The SELECT can reference any accessible table, apply filters, join tables, and transform columns. The column list in the INSERT must match the shape of the SELECT output in count and compatible types.

-- Archive inactive products into a separate audit table
CREATE TABLE products_archive (
    product_id    NUMBER(10)   NOT NULL,
    product_name  VARCHAR2(150) NOT NULL,
    category      VARCHAR2(60)  NOT NULL,
    unit_price    NUMBER(10,2)  NOT NULL,
    archived_date DATE          DEFAULT SYSDATE NOT NULL
);

-- Copy all inactive products from products into the archive table
INSERT INTO products_archive (product_id, product_name, category, unit_price, archived_date)
SELECT product_id,
       product_name,
       category,
       unit_price,
       SYSDATE
FROM   products
WHERE  active_flag = 'N';

-- Copy employees hired in 2023 into a reporting snapshot table
CREATE TABLE employees_2023_snapshot (
    employee_id  NUMBER(6)     NOT NULL,
    full_name    VARCHAR2(101) NOT NULL,
    job_id       VARCHAR2(20)  NOT NULL,
    hire_date    DATE          NOT NULL
);

INSERT INTO employees_2023_snapshot (employee_id, full_name, job_id, hire_date)
SELECT employee_id,
       first_name || ' ' || last_name,   -- concatenation using Oracle's || operator
       job_id,
       hire_date
FROM   employees
WHERE  hire_date >= DATE '2023-01-01'
AND    hire_date <  DATE '2024-01-01';

SELECT COUNT(*) AS rows_inserted FROM employees_2023_snapshot;

• INSERT INTO SELECT does not use a VALUES clause — the SELECT replaces it entirely; combining both in the same statement is a syntax error
• The || operator concatenates strings in Oracle — first_name || ' ' || last_name produces a single full name string; this is the Oracle equivalent of SQL Server's + operator
• If the SELECT returns zero rows, the INSERT succeeds with zero rows inserted — no error is raised; always verify the row count after bulk inserts

RETURNING INTO

RETURNING INTO captures column values from the row just inserted without requiring a follow-up SELECT. It is most useful in PL/SQL blocks where the generated primary key needs to be used immediately — for example to insert child rows or to pass the new ID back to an application. In a SQL*Plus or SQL Developer session, bind variables receive the returned value.

-- RETURNING INTO in a PL/SQL anonymous block
-- Captures the generated customer_id immediately after insert
DECLARE
    v_customer_id  customers.customer_id%TYPE;
    v_order_id     orders.order_id%TYPE;
BEGIN
    INSERT INTO customers (customer_id, full_name, email, country)
    VALUES (seq_customer_id.NEXTVAL, 'Yuki Tanaka', 'yuki@example.com', 'Japan')
    RETURNING customer_id INTO v_customer_id;

    -- Use the captured ID immediately to insert the related order
    INSERT INTO orders (order_id, customer_id, status, total_amount)
    VALUES (seq_order_id.NEXTVAL, v_customer_id, 'pending', 0)
    RETURNING order_id INTO v_order_id;

    DBMS_OUTPUT.PUT_LINE('Customer ID: ' || v_customer_id);
    DBMS_OUTPUT.PUT_LINE('Order ID:    ' || v_order_id);
END;
/

• RETURNING INTO only works for single-row inserts — for bulk inserts use FORALL with RETURNING BULK COLLECT INTO in PL/SQL
• %TYPE declares a variable with the same data type as the referenced column — if the column type changes, the variable type updates automatically without changing the PL/SQL code
• RETURNING INTO is more efficient than inserting and then selecting — it avoids the second round trip to the database and is immune to the race condition where another session could insert the same ID between your INSERT and your SELECT

Constraint Violations on INSERT

Understanding which error Oracle raises for each constraint violation makes debugging much faster. Each constraint type produces a specific error code, and the error message always includes the constraint name — which is why naming constraints in Lesson 11 matters.

-- ORA-00001: unique constraint violated — duplicate primary key
INSERT INTO customers (customer_id, full_name, email, country)
VALUES (13, 'Duplicate', 'dup@example.com', 'USA');

-- ORA-01400: NOT NULL constraint violated — required column missing
INSERT INTO customers (customer_id, full_name, country)
VALUES (seq_customer_id.NEXTVAL, 'No Email', 'USA');

-- ORA-02291: parent key not found — customer_id 9999 does not exist
INSERT INTO orders (order_id, customer_id, status, total_amount)
VALUES (seq_order_id.NEXTVAL, 9999, 'pending', 0);

-- ORA-02290: check constraint violated — negative price
INSERT INTO products (product_id, product_name, category, unit_price, stock_qty, active_flag)
VALUES (seq_product_id.NEXTVAL, 'Bad Product', 'Electronics', -10.00, 0, 'Y');

• Every error message includes the schema and constraint name in parentheses — SCHEMA.PK_CUSTOMERS immediately tells you which table and which constraint failed without needing to look anything up
• Oracle rolls back only the failed statement, not the entire transaction — other inserts in the same session that have not yet been committed remain pending and can still be committed or rolled back
• To insert a large batch and skip rows that violate constraints rather than stopping, use a FORALL loop with the SAVE EXCEPTIONS clause in PL/SQL — plain SQL has no built-in skip-on-error mechanism

Summary

Practice Questions

Quiz

Next up — SELECT Queries — Learn how to retrieve data from the DataplexaStore and DataplexaHR tables using SELECT, WHERE, ORDER BY, and column expressions.