Ad – 728×90
✏️ Data Modification

SQL INSERT – Adding Data to Tables

INSERT INTO is the SQL statement for adding new rows to a table. You can insert a single row, multiple rows at once, or copy entire result sets from other tables. It is one of the three core DML (Data Manipulation Language) statements alongside UPDATE and DELETE.

⏱️ 20 min read 🎯 Beginner 📅 Updated 2026

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
Ad – 336×280

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

📋 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.
🗄️ UPDATE Data 🗄️ DELETE Data 🗄️ UPSERT 🗄️ Transactions

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.

Ad – 728×90