SQL INSERT INTO Statement

1. Overview

The INSERT statement adds new rows to a database table. It can insert single or multiple rows, and supports default values, explicit column lists, and inserting results from a query.

2. Basic Single-row INSERT

INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
VALUES (1, 'Alice', 'Wong', '2025-05-17', 75000);

Explanation: Specifies target table and columns, then lists values in the same order. All NOT NULL columns without defaults must be provided.

Resulting Table Rows

3. INSERT Without Column List

INSERT INTO employees
VALUES (2, 'Bob', 'Patel', '2025-05-18', 80000);

Explanation: Values must match table column order exactly. Risky if schema changes; explicit lists are safer.

4. Multiple-row INSERT

INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
VALUES
  (3, 'Cathy', 'Lee', '2025-05-19', 62000),
  (4, 'Dan',   'Smith','2025-05-20', 90000);

Explanation: Batch insertion of multiple rows reduces round-trips and improves performance.

Resulting Rows

5. INSERT Using SELECT

INSERT INTO high_earners (employee_id, first_name, salary)
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > 80000;

Explanation: Copies qualifying rows into another table. Useful for archiving or denormalization.

High Earners

6. DEFAULT Values

Columns with DEFAULT definitions can be omitted in INSERT.

-- Table: logs(log_id SERIAL, message TEXT DEFAULT 'N/A', created TIMESTAMP DEFAULT NOW())
INSERT INTO logs (log_id) VALUES (1);

Resulting Row: message='N/A', created=current timestamp.

7. Handling Conflicts (Upsert)

-- PostgreSQL syntax
INSERT INTO employees (employee_id, salary)
VALUES (1, 80000)
ON CONFLICT (employee_id) DO UPDATE
  SET salary = EXCLUDED.salary;

Explanation: Inserts new or updates existing row on primary key conflict.

8. Performance Tips

9. Summary

The INSERT statement is versatile for single, batch, query-driven, and upsert scenarios. Leveraging defaults and handling conflicts efficiently ensures robust data loading patterns.