Lesson 17 Intermediate Level
17 of 30 lessons

INSERT Statement

Learn how to insert data into tables using INSERT statements with examples. The INSERT statement is essential for adding data to your database.

Introduction

The INSERT statement is used to add new rows into a table. You can insert values into all or selected columns.

Basic Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example

-- Insert a single row into employees
INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
VALUES (101, 'John', 'Doe', TO_DATE('2024-01-01', 'YYYY-MM-DD'), 5000);

Insert Without Column List

You can omit the column list if you provide values for all columns in the correct order: 

INSERT INTO employees
VALUES (102, 'Jane', 'Smith', TO_DATE('2024-01-15', 'YYYY-MM-DD'), 5500);

Inserting Multiple Rows (Oracle 12c+)

INSERT ALL
  INTO employees (employee_id, first_name, last_name, hire_date, salary)
  VALUES (103, 'Alice', 'Wong', SYSDATE, 6000)
  INTO employees (employee_id, first_name, last_name, hire_date, salary)
  VALUES (104, 'Bob', 'Lee', SYSDATE, 6200)
SELECT * FROM dual;

Best Practices

  • Always match column order and data types
  • Use TO_DATE() for date values
  • Avoid inserting NULLs unintentionally
  • Use transactions (COMMIT/ROLLBACK) for data safety

Practice Task