Colevate Online Tech Tutorials

INSERT Statements

The INSERT statement is used to add new rows of data into a table. You can insert a single row, specific columns, or multiple rows from another query. It is part of data manipulation language (DML).

Inserting a Single Row

This method adds one complete row to a table, providing values for all columns in the order they appear in the table definition. It's a straightforward way to populate a table with new records.

Example 1: Oracle 19c SQL INSERT - Single Row

INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary)
VALUES (207, 'Sarah', 'Connor', 'SARAH.C@EXAMPLE.COM', '515.123.4567', SYSDATE, 'IT_PROG', 7000);
-- Inserts a new employee record with all specified column values.

Explanation This code inserts a single row into the employees table. All specified column values are provided in the VALUES clause, ensuring a complete new record. This is a common method for adding individual entries.

Inserting Specific Columns

You can choose to insert data into only a subset of columns within a table. This is useful when some columns have default values or are nullable, allowing for partial data entry.

Example 2: Oracle 19c SQL INSERT - Specific Columns

INSERT INTO departments (department_id, department_name, location_id)
VALUES (280, 'Customer Support', 1700);
-- Inserts a new department, only specifying its ID, name, and location.

Explanation This code inserts a new row into the departments table, only providing values for department_id, department_name, and location_id. Any other columns in the departments table would either take their default values or be set to NULL if allowed.

INSERT INTO ... SELECT ... (Inserting from another query)

This powerful INSERT statement allows you to populate a table with data retrieved directly from another query. It's incredibly useful for data migration, archiving, or creating summary tables.

Example 3: Oracle 19c SQL INSERT - From SELECT

INSERT INTO new_hires (employee_id, first_name, last_name, hire_date)
SELECT employee_id, first_name, last_name, hire_date
FROM employees
WHERE hire_date > '01-JAN-2025';
-- Populates the new_hires table with employee data from the employees table for recent hires.

Explanation This statement inserts data into the new_hires table by selecting specific columns from the employees table. Only employees hired after January 1st, 2025, are included, demonstrating data transfer based on a condition.

INSERT ALL and INSERT FIRST (Multi-table Inserts)

Oracle's INSERT ALL and INSERT FIRST allow you to insert data into multiple tables with a single SQL statement. INSERT ALL inserts into all specified tables, while INSERT FIRST inserts into the first table whose WHEN condition is met. These are efficient for distributing data.

Example 1: Oracle 19c SQL INSERT ALL

INSERT ALL
  WHEN salary < 5000 THEN
    INTO junior_employees (emp_id, emp_name, emp_salary) VALUES (employee_id, first_name || ' ' || last_name, salary)
  WHEN salary >= 5000 AND salary < 10000 THEN
    INTO mid_level_employees (emp_id, emp_name, emp_salary) VALUES (employee_id, first_name || ' ' || last_name, salary)
SELECT employee_id, first_name, last_name, salary
FROM employees;
-- Inserts employees into different tables based on their salary ranges.

Explanation This INSERT ALL statement selects data from the employees table and conditionally inserts rows into either junior_employees or mid_level_employees based on the employee's salary. Rows satisfying a condition are inserted into the corresponding table.

Example 2: Oracle 19c SQL INSERT FIRST

INSERT FIRST
  WHEN department_id = 60 THEN
    INTO it_dept_employees (emp_id, emp_name) VALUES (employee_id, first_name || ' ' || last_name)
  WHEN department_id = 90 THEN
    INTO exec_dept_employees (emp_id, emp_name) VALUES (employee_id, first_name || ' ' || last_name)
  ELSE
    INTO other_employees (emp_id, emp_name) VALUES (employee_id, first_name || ' ' || last_name)
SELECT employee_id, first_name, last_name, department_id
FROM employees;
-- Inserts employees into the first table whose department condition is met, otherwise into 'other_employees'.

Explanation This INSERT FIRST statement processes rows from employees and inserts them into the first target table (it_dept_employees, exec_dept_employees, or other_employees) whose WHEN condition is true. Once a condition is met, the row is inserted, and no further WHEN clauses are evaluated for that row.

Example 3: Oracle 19c SQL INSERT ALL (Unconditional Multi-table Insert)

INSERT ALL
  INTO sales_reps (employee_id, first_name, last_name) VALUES (employee_id, first_name, last_name)
  INTO all_employees_backup (employee_id, first_name, last_name, hire_date) VALUES (employee_id, first_name, last_name, hire_date)
SELECT employee_id, first_name, last_name, hire_date
FROM employees
WHERE job_id = 'SA_REP';
-- Inserts sales representatives into two different tables unconditionally.

Explanation This INSERT ALL example demonstrates an unconditional multi-table insert. All rows from the SELECT statement that meet the WHERE clause (sales representatives) are inserted into both sales_reps and all_employees_backup tables simultaneously.

MERGE INTO (Upsert operations: INSERT or UPDATE based on a condition)

The MERGE statement (often called "upsert") allows you to perform INSERT or UPDATE operations on a target table based on whether a row matches a source table's data. It's ideal for synchronizing data or applying changes efficiently.

Example 1: Oracle 19c SQL MERGE - Update Existing, Insert New

MERGE INTO products target
USING new_product_data source
ON (target.product_id = source.product_id)
WHEN MATCHED THEN
  UPDATE SET target.price = source.price, target.quantity = target.quantity + source.quantity
WHEN NOT MATCHED THEN
  INSERT (product_id, product_name, price, quantity) VALUES (source.product_id, source.product_name, source.price, source.quantity);
-- Updates product price and quantity if product exists, otherwise inserts a new product.

Explanation This MERGE statement attempts to match products with new_product_data by product_id. If a match is found, the price is updated, and quantity is increased. If no match, a new product record is inserted.

Example 2: Oracle 19c SQL MERGE - Update Only Matched Rows

MERGE INTO inventory target
USING daily_sales source
ON (target.item_id = source.item_id)
WHEN MATCHED THEN
  UPDATE SET target.stock_level = target.stock_level - source.items_sold
  DELETE WHERE target.stock_level <= 0;
-- Decreases inventory stock based on sales; deletes item from inventory if stock drops to zero or below.

Explanation This MERGE statement updates the stock_level in the inventory table based on daily_sales. If a match occurs, the stock is reduced. Additionally, if the stock_level drops to zero or below after the update, the row is deleted from inventory.

Example 3: Oracle 19c SQL MERGE - Insert Only New Rows

MERGE INTO customers target
USING new_customer_leads source
ON (target.email = source.email)
WHEN NOT MATCHED THEN
  INSERT (customer_id, first_name, last_name, email, registration_date)
  VALUES (customer_seq.NEXTVAL, source.first_name, source.last_name, source.email, SYSDATE);
-- Inserts new customers from leads if their email address does not already exist in the customers table.

Explanation This MERGE statement is configured to only perform inserts. It attempts to match customers with new_customer_leads based on email. If no matching email is found in the customers table, a new customer record is inserted using a sequence for the customer_id.