SQL INSERT – Adding Data to Tables

Basic INSERT Syntax

Always specify the column list explicitly — it makes your code resilient to schema changes:

SQL

-- Explicit column list (recommended)
INSERT INTO users (name, email, created_at)
VALUES ('Alice', 'alice@example.com', NOW());

-- Omitting column list (NOT recommended — order-dependent)
INSERT INTO users
VALUES (DEFAULT, 'Bob', 'bob@example.com', NOW());
-- If the table schema changes, column order may break your insert

💡

Always specify the column list

If you omit the column list, you must provide values in the exact order all columns are defined in the table, including auto-increment columns. A schema change (adding or reordering a column) will silently insert wrong data. Always name the columns you are inserting.

Inserting Multiple Rows

Inserting multiple rows in a single statement is far more efficient than separate INSERT statements — one round-trip to the database instead of N:

SQL

INSERT INTO products (name, category, price, stock)
VALUES
    ('Laptop Pro 15',  'Electronics', 1299.99, 50),
    ('Wireless Mouse', 'Electronics',   29.99, 200),
    ('USB-C Hub',      'Accessories',   49.99, 150),
    ('Desk Lamp',      'Office',         24.99, 300);
-- All four rows inserted in a single statement

INSERT SELECT – Copying Data

INSERT ... SELECT copies rows from one query result into a table. No VALUES clause is used:

SQL

-- Archive old orders to a separate archive table
INSERT INTO orders_archive (id, customer_id, amount, order_date, archived_at)
SELECT id, customer_id, amount, order_date, NOW()
FROM orders
WHERE order_date < '2023-01-01'
  AND status = 'completed';

-- Copy a subset of users into a leads table
INSERT INTO leads (email, signup_date, source)
SELECT email, created_at, 'organic'
FROM users
WHERE referral_source IS NULL
  AND created_at >= '2025-01-01';

Default Values and NULL

Omitting a column uses its DEFAULT value (if defined) or NULL (if the column allows NULL). You can also use the DEFAULT keyword explicitly:

SQL

-- Table definition (for context)
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,            -- auto-increment, do not insert
    customer_id INT    NOT NULL,
    status      VARCHAR(20) DEFAULT 'pending', -- default value
    notes       TEXT,                          -- nullable, defaults to NULL
    created_at  TIMESTAMP DEFAULT NOW()        -- default to current time
);

-- Insert: omit auto-increment and columns with defaults
INSERT INTO orders (customer_id)
VALUES (42);
-- Result: status='pending', notes=NULL, created_at=NOW(), id auto-assigned

-- Explicitly use DEFAULT keyword
INSERT INTO orders (customer_id, status, created_at)
VALUES (43, DEFAULT, DEFAULT);
-- status='pending', created_at=NOW()

RETURNING Clause (PostgreSQL)

PostgreSQL's RETURNING clause lets you retrieve data from the inserted rows — including auto-generated IDs — without a separate SELECT query:

SQL

-- PostgreSQL: insert and get the generated ID back
INSERT INTO users (name, email)
VALUES ('Carol', 'carol@example.com')
RETURNING id, created_at;
-- Returns: id=1042, created_at=2026-06-08 14:30:00

-- Insert multiple rows and return all generated IDs
INSERT INTO products (name, price)
VALUES ('Widget A', 9.99), ('Widget B', 14.99)
RETURNING id, name;
-- Returns two rows with their new IDs

-- MySQL equivalent: use LAST_INSERT_ID() after the insert
INSERT INTO users (name, email) VALUES ('Dave', 'dave@example.com');
SELECT LAST_INSERT_ID();  -- returns the last auto-increment ID

ℹ️

Getting inserted IDs in different databases

PostgreSQL: RETURNING id. MySQL: LAST_INSERT_ID(). SQL Server: OUTPUT INSERTED.id. SQLite: last_insert_rowid(). Each approach is database-specific; application frameworks (ORM libraries) abstract this for you.

Summary

INSERT: Single Rows, Bulk, and From a Query

INSERT adds rows. Always name the columns — relying on positional order breaks the moment someone adds or reorders a column.

-- name columns explicitly (robust)
INSERT INTO users (name, email) VALUES ('Ann', 'a@b.com');

-- multi-row: one statement, many rows — far faster than N statements
INSERT INTO users (name, email) VALUES
  ('Bob', 'b@b.com'),
  ('Cy',  'c@b.com');

-- insert the result of a query
INSERT INTO archive (id, name)
SELECT id, name FROM users WHERE active = false;

Feature	Note
omitted columns	get their `DEFAULT` (or NULL)
auto id	skip `SERIAL`/identity columns — DB fills them
`RETURNING id`	get the new row's id back (Postgres)

Performance: inserting 1000 rows in one multi-row statement (or a single transaction) is dramatically faster than 1000 separate INSERTs — each statement otherwise pays its own round-trip and commit. Gotcha: a value violating a constraint (duplicate unique key, missing FK) rejects the whole statement — use ON CONFLICT (upsert) if you want "insert or update instead."

🏋️ Practical Exercise

Insert a single row with INSERT INTO ... VALUES.
Insert multiple rows in one statement.
Insert into specific columns only.
Insert from another table with INSERT ... SELECT.
Verify the result with a SELECT.

🔥 Challenge Exercise

Insert three customers into a customers table in a single statement, then insert one more using only some columns (letting the rest default). Explain the difference between listing columns explicitly and relying on positional order.

📋 Summary

INSERT INTO table (col1, col2) VALUES (...) — always specify the column list.
Multi-row insert: VALUES (...), (...), (...) — efficient for bulk inserts.
INSERT SELECT: copies rows from a query result — no VALUES clause.
Omitting a column inserts its DEFAULT value or NULL.
RETURNING (PostgreSQL) retrieves inserted row data (including auto-generated IDs) without an extra SELECT.

Interview Questions

What is the syntax of an INSERT statement?
How do you insert multiple rows at once?
What happens to columns you omit in an INSERT?
How do you insert data selected from another table?
What is the difference between INSERT and UPSERT?

FAQ

What happens if I INSERT a duplicate primary key? +

The database raises a unique constraint violation error and the insert fails — no row is inserted. To handle duplicates gracefully, use UPSERT syntax: INSERT ... ON CONFLICT DO UPDATE (PostgreSQL), INSERT ... ON DUPLICATE KEY UPDATE (MySQL), or INSERT OR REPLACE (SQLite). See the UPSERT lesson for full details.

Can INSERT SELECT insert into the same table it selects from? +

In most databases you can, but you must be careful about infinite loops or reading uncommitted inserts. MySQL requires an intermediate derived table: INSERT INTO t SELECT * FROM (SELECT ... FROM t WHERE ...) AS tmp. PostgreSQL and SQL Server handle same-table INSERT SELECT directly, reading a snapshot of the table before the insert begins.

Is there a limit to how many rows I can insert in one statement? +

There is no SQL standard limit, but practical limits depend on the database's maximum statement/packet size. MySQL has a max_allowed_packet setting (default 64MB). For very large bulk inserts (millions of rows), use database-specific bulk load tools: PostgreSQL's COPY FROM, MySQL's LOAD DATA INFILE, or SQL Server's BULK INSERT — these are orders of magnitude faster than multi-row VALUES inserts.