SQL 25 🛢️ Inserting Single and Multiple Rows with INSERT INTO

A table is empty when it is created. The INSERT INTO statement is the first DML statement that fills it with data. It adds one or more rows to a table. Every row that an application stores, every record that a user saves, every event that a system logs, arrives through an INSERT statement. It is the most frequently executed write operation in any database.

The previous chapters covered DQL — the SELECT statement and its clauses. This chapter begins the DML portion of the series. The INSERT INTO statement is the first of the four core DML statements: INSERT, UPDATE, DELETE, and MERGE. It is the simplest of the four, but it has subtleties: the column list, the value order, the defaults, the auto-increment behavior, and the bulk insert performance.

Key point: The INSERT INTO statement adds rows to a table. It does not replace existing rows and it does not check for duplicates unless a constraint requires it. If the table has a primary key and you insert a row with a duplicate key, the statement fails. If the table has a NOT NULL constraint and you omit a required column, the statement fails. The database enforces the constraints on every insert.

---

Why INSERT Matters

Data enters a database through INSERT. There is no other way. The UPDATE statement modifies existing rows. The DELETE statement removes rows. The INSERT statement is the only statement that adds new ones.

The persistence problem. An application that receives data — a form submission, an API request, a sensor reading — must store it. The INSERT statement is the mechanism that writes the data to the table. Without it, the data exists only in the application’s memory and is lost when the process ends.

The bulk problem. An application that receives thousands of records per second must insert them efficiently. A single INSERT statement per row is slow. A multi-row INSERT statement inserts many rows in a single operation. The choice matters for performance.

The constraint problem. The table has constraints: primary keys, foreign keys, NOT NULL, UNIQUE, CHECK, and defaults. The INSERT statement must satisfy them. If it does not, the database rejects the row and the statement fails. The constraints are the rules that keep the data consistent.

The transaction problem. An INSERT statement is part of a transaction. If the transaction is rolled back, the inserted rows are removed. If the transaction is committed, the rows are permanent. The transaction boundary determines the visibility and the durability of the inserted data.

The trade-off. The INSERT statement is simple in concept but has many variants: single-row, multi-row, INSERT ... SELECT, INSERT ... RETURNING, and upsert. Each variant solves a different problem. The choice depends on the source of the data, the volume, and the desired behavior on conflict.

---

a. The Basic INSERT Statement

The INSERT INTO statement adds a single row to a table. The syntax has three parts: the table name, the column list, and the values.

INSERT INTO customers (customer_id, first_name, last_name, email)
VALUES (1, 'Alice', 'Johnson', 'alice@example.com');

The table name follows the INSERT INTO keywords. The column list is a parenthesized list of columns. The VALUES keyword introduces the value list. The value list is a parenthesized list of values in the same order as the columns.

The column list is optional. If it is omitted, the values must be in the order of the columns in the table definition.

INSERT INTO customers
VALUES (1, 'Alice', 'Johnson', 'alice@example.com');

The statement inserts a row with values for every column in the table, in the order the columns were defined. This form is fragile: adding a column to the table or reordering the columns breaks the statement. The explicit column list is the recommended form.

The value list must match the column list in number and in type. Each value must be compatible with the column’s data type. If a value is a string, it is enclosed in single quotes. If a value is a number, it is written without quotes. If a value is a date, it is written in the ISO format ('2026-01-15').

INSERT INTO orders (order_id, customer_id, order_date, total)
VALUES (1001, 1, '2026-01-15', 99.99);

The statement inserts an order row. The order_date value is a date literal. The total value is a decimal literal.

The INSERT statement can insert a row with values for only some columns. The omitted columns must have defaults or allow NULL.

INSERT INTO customers (customer_id, first_name, last_name)
VALUES (2, 'Bob', 'Smith');

The email column is omitted. If the column has a default value, the default is used. If the column is nullable, NULL is used. If the column is NOT NULL and has no default, the statement fails.

---

b. Multi-Row INSERT

A single INSERT INTO statement can add multiple rows. The column list is followed by multiple parenthesized value lists, separated by commas.

INSERT INTO customers (customer_id, first_name, last_name, email)
VALUES
    (1, 'Alice', 'Johnson', 'alice@example.com'),
    (2, 'Bob', 'Smith', 'bob@example.com'),
    (3, 'Carol', 'Williams', 'carol@example.com');

The statement inserts three rows in a single operation. The multi-row form is significantly faster than three separate INSERT statements because it reduces the round trips between the application and the database. The database parses the statement once, prepares the plan once, and executes the insert for each row.

The multi-row form is supported by PostgreSQL, MySQL, SQL Server, and Oracle. MySQL also supports the INSERT INTO ... VALUES syntax with a comma-separated list of value lists. PostgreSQL supports the same syntax.

For very large inserts, the multi-row form has limits. A single statement with thousands of value lists can exceed the maximum packet size or the maximum statement length. The recommended approach for bulk inserts is to use the COPY command in PostgreSQL or the LOAD DATA command in MySQL. These commands are designed for loading large amounts of data from files.

The multi-row form can also be combined with the INSERT ... SELECT statement to insert the result of a query.

INSERT INTO customers_archive (customer_id, first_name, last_name, email)
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE created_at < '2025-01-01';

The statement inserts the rows from the customers table that match the WHERE clause into the customers_archive table. The column list of the INSERT statement must match the column list of the SELECT statement in number and in type.

---

c. Defaults, Auto-Increment, and RETURNING

The INSERT statement interacts with three features: defaults, auto-increment columns, and the RETURNING clause.

Defaults. A column can have a DEFAULT constraint. When the INSERT statement omits the column, the default value is used.

INSERT INTO customers (customer_id, first_name, last_name)
VALUES (3, 'Carol', 'Williams');

If the email column has a default of 'unknown@example.com', the row is inserted with that value. If the created_at column has a default of CURRENT_TIMESTAMP, the row is inserted with the current timestamp.

Auto-increment columns. A column can be declared as SERIAL (PostgreSQL), AUTO_INCREMENT (MySQL), or IDENTITY (SQL Server). When the INSERT statement omits the column, the database generates the next value automatically.

INSERT INTO customers (first_name, last_name, email)
VALUES ('Dave', 'Brown', 'dave@example.com');

The customer_id column is omitted. The database generates the next value. The application does not need to know the value.

RETURNING. PostgreSQL, SQL Server, and Oracle support the RETURNING clause. The clause returns the values of the inserted row.

INSERT INTO customers (first_name, last_name, email)
VALUES ('Eve', 'Davis', 'eve@example.com')
RETURNING customer_id, created_at;

The statement inserts the row and returns the customer_id and the created_at. The RETURNING clause is useful when the application needs to know the generated values.

PostgreSQL returns the values of the inserted row. The RETURNING * form returns all columns. The RETURNING column_name form returns the specified columns.

MySQL does not support the RETURNING clause. The LAST_INSERT_ID() function returns the ID of the last inserted row. The function is session-scoped and works only for auto-increment columns.

SQL Server supports the OUTPUT clause instead of RETURNING.

INSERT INTO customers (first_name, last_name, email)
OUTPUT inserted.customer_id, inserted.created_at
VALUES ('Frank', 'Miller', 'frank@example.com');

The OUTPUT clause returns the inserted values. The inserted prefix identifies the inserted row.

Database	Returning Syntax
PostgreSQL	RETURNING column
SQL Server	OUTPUT inserted.column
Oracle	RETURNING column INTO variable
MySQL	LAST_INSERT_ID()

---

Complete Example Session

This session demonstrates the INSERT INTO statement on a small table.

-- ============================================
-- PART 1: THE TABLE
-- ============================================

CREATE TABLE customers (
    customer_id  SERIAL PRIMARY KEY,
    first_name   VARCHAR(50) NOT NULL,
    last_name    VARCHAR(50) NOT NULL,
    email        VARCHAR(100) UNIQUE,
    city         VARCHAR(50),
    is_active    BOOLEAN NOT NULL DEFAULT TRUE,
    created_at   TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- customer_id: auto-incrementing primary key
-- first_name, last_name: required
-- email: unique, nullable
-- city: nullable
-- is_active: defaults to true
-- created_at: defaults to the current timestamp

-- ============================================
-- PART 2: SINGLE-ROW INSERT
-- ============================================

INSERT INTO customers (first_name, last_name, email, city)
VALUES ('Alice', 'Johnson', 'alice@example.com', 'New York');

-- The customer_id is generated automatically.
-- The is_active column uses the default (true).
-- The created_at column uses the default.

-- ============================================
-- PART 3: VERIFY THE INSERT
-- ============================================

SELECT * FROM customers WHERE email = 'alice@example.com';

-- Output:
-- 1 | Alice | Johnson | alice@example.com | New York | t | 2026-10-02 12:00:00+00

-- ============================================
-- PART 4: MULTI-ROW INSERT
-- ============================================

INSERT INTO customers (first_name, last_name, email, city)
VALUES
    ('Bob', 'Smith', 'bob@example.com', 'Chicago'),
    ('Carol', 'Williams', 'carol@example.com', 'New York'),
    ('Dave', 'Brown', 'dave@example.com', 'Boston');

-- Three rows inserted in a single statement.

SELECT * FROM customers;

-- Output: four rows.

-- ============================================
-- PART 5: INSERT WITH DEFAULT
-- ============================================

INSERT INTO customers (first_name, last_name)
VALUES ('Eve', 'Davis');

-- The email, city, is_active, and created_at columns use their defaults.
-- The email is NULL (no default, nullable).
-- The city is NULL.

SELECT * FROM customers WHERE first_name = 'Eve';

-- ============================================
-- PART 6: INSERT WITH NULL
-- ============================================

INSERT INTO customers (first_name, last_name, email, city)
VALUES ('Frank', 'Miller', NULL, NULL);

-- The email and city are explicitly set to NULL.

-- ============================================
-- PART 7: THE UNIQUE CONSTRAINT
-- ============================================

INSERT INTO customers (first_name, last_name, email)
VALUES ('Grace', 'Wilson', 'alice@example.com');

-- Error: duplicate key value violates unique constraint "customers_email_key"

-- The email 'alice@example.com' already exists.
-- The UNIQUE constraint rejects the duplicate.

-- ============================================
-- PART 8: THE NOT NULL CONSTRAINT
-- ============================================

INSERT INTO customers (last_name, email)
VALUES ('Moore', 'henry@example.com');

-- Error: null value in column "first_name" violates not-null constraint

-- The first_name column is required.

-- ============================================
-- PART 9: THE RETURNING CLAUSE
-- ============================================

INSERT INTO customers (first_name, last_name, email)
VALUES ('Henry', 'Moore', 'henry@example.com')
RETURNING customer_id, created_at;

-- Output:
-- 7 | 2026-10-02 12:05:00+00

-- The statement inserts the row and returns the generated values.

-- ============================================
-- PART 10: THE INSERT ... SELECT PATTERN
-- ============================================

CREATE TABLE customers_archive (
    customer_id INT PRIMARY KEY,
    first_name  VARCHAR(50),
    last_name   VARCHAR(50),
    email       VARCHAR(100)
);

INSERT INTO customers_archive (customer_id, first_name, last_name, email)
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE is_active = TRUE;

-- The statement copies the active customers to the archive table.

The ten parts cover the table, single-row insert, verify the insert, multi-row insert, insert with default, insert with NULL, the unique constraint, the NOT NULL constraint, the RETURNING clause, and the INSERT ... SELECT pattern.

---

Quick Reference

The INSERT Syntax

Form	Purpose
INSERT INTO t (c1, c2) VALUES (v1, v2)	Single row
INSERT INTO t (c1, c2) VALUES (v1, v2), (v3, v4)	Multiple rows
INSERT INTO t SELECT ... FROM s	Insert from query
INSERT INTO t (c1) VALUES (v1) RETURNING c2	Insert and return
INSERT INTO t DEFAULT VALUES	Insert all defaults

The Column List

Form	Behavior
Explicit column list	Values match the list order
No column list	Values match the table order
Omitted columns	Use defaults or NULL

The Constraint Behavior

Constraint	Behavior on Insert
PRIMARY KEY	Duplicate key fails
UNIQUE	Duplicate value fails
NOT NULL	Null value fails
CHECK	False condition fails
FOREIGN KEY	Missing parent fails
DEFAULT	Used when column omitted

The Returning Variants

Database	Syntax
PostgreSQL	RETURNING column
SQL Server	OUTPUT inserted.column
Oracle	RETURNING column INTO variable
MySQL	LAST_INSERT_ID()

---

Best Practices

✅ Do This:

-- Use the explicit column list
INSERT INTO customers (first_name, last_name, email) VALUES (...) -- ✅

-- Use multi-row insert for bulk data
INSERT INTO customers (first_name, last_name) VALUES (...), (...), (...) -- ✅

-- Use RETURNING to get generated values
INSERT INTO customers (first_name) VALUES ('Alice') RETURNING customer_id -- ✅

-- Use INSERT ... SELECT for bulk copies
INSERT INTO archive SELECT * FROM source WHERE ...               -- ✅

❌ Don’t Do This:

-- Don't omit the column list
INSERT INTO customers VALUES (...)  -- fragile                    -- ❌

-- Don't insert duplicates
INSERT INTO customers (email) VALUES ('alice@example.com')       -- ⚠️

-- Don't insert NULL into NOT NULL columns
INSERT INTO customers (first_name) VALUES (NULL)                 -- ❌

-- Don't assume RETURNING works everywhere
INSERT INTO customers (...) RETURNING id  -- fails in MySQL      -- ❌

---

Common Pitfalls

Pitfall	Why It Happens	Fix
Column count mismatch	Wrong number of values	Match the column list
Duplicate key error	Primary key or unique violation	Use a unique value
NOT NULL violation	Required column omitted	Provide the value
Foreign key violation	Parent row missing	Insert the parent first
RETURNING not supported	Wrong database	Use LAST_INSERT_ID() or OUTPUT

---

Real-World Examples

1. Single Row Insert

INSERT INTO customers (first_name, last_name, email) VALUES ('Alice', 'Johnson', 'alice@example.com');

2. Multi-Row Insert

INSERT INTO customers (first_name, last_name) VALUES ('Bob', 'Smith'), ('Carol', 'Williams');

3. Insert with Default

INSERT INTO orders (customer_id, total) VALUES (1, 99.99);

4. Insert with NULL

INSERT INTO customers (first_name, last_name, email) VALUES ('Dave', 'Brown', NULL);

5. Insert with RETURNING

INSERT INTO customers (first_name) VALUES ('Eve') RETURNING customer_id;

6. Insert from Query

INSERT INTO customers_archive SELECT * FROM customers WHERE created_at < '2025-01-01';

7. Insert with Auto-Increment

INSERT INTO customers (first_name, last_name) VALUES ('Frank', 'Miller');

8. Insert All Defaults

INSERT INTO customers DEFAULT VALUES;

9. Insert with a Subquery

INSERT INTO order_items (order_id, product_id, quantity) VALUES (1001, (SELECT product_id FROM products WHERE name = 'Laptop'), 1);

10. Bulk Insert with COPY (PostgreSQL)

COPY customers FROM '/path/to/data.csv' WITH CSV HEADER;

---

Visual

The INSERT Statement

┌──────────────────────────────────────────────┐
│  INSERT INTO table_name (col1, col2, col3)   │
│  VALUES (val1, val2, val3);                  │
│                                              │
│  The column list specifies the target columns.│
│  The value list specifies the values.        │
│  The order must match.                       │
│                                              │
└──────────────────────────────────────────────┘

The Multi-Row Insert

┌──────────────────────────────────────────────┐
│  INSERT INTO customers (name, email)         │
│  VALUES                                      │
│    ('Alice', 'alice@example.com'),           │
│    ('Bob', 'bob@example.com'),               │
│    ('Carol', 'carol@example.com');           │
│                                              │
│  One statement, three rows.                  │
│  Faster than three separate statements.      │
│                                              │
└──────────────────────────────────────────────┘

The Constraint Enforcement

┌──────────────────────────────────────────────┐
│  INSERT INTO customers (email)               │
│  VALUES ('alice@example.com');               │
│                                              │
│  Database checks:                            │
│    ├─ Primary key unique?                    │
│    ├─ Unique columns unique?                 │
│    ├─ NOT NULL columns have values?          │
│    ├─ CHECK conditions true?                 │
│    └─ Foreign keys reference valid rows?     │
│                                              │
│  If any check fails, the insert is rejected. │
│                                              │
└──────────────────────────────────────────────┘

The RETURNING Clause

┌──────────────────────────────────────────────┐
│  INSERT INTO customers (first_name)          │
│  VALUES ('Alice')                            │
│  RETURNING customer_id, created_at;          │
│                                              │
│  Output:                                     │
│    1 | 2026-10-02 12:00:00+00                │
│                                              │
│  The application receives the generated      │
│  values without a separate SELECT.           │
│                                              │
└──────────────────────────────────────────────┘

---

Summary

Item	Value
INSERT INTO	Add rows to a table
Single-row	One value list
Multi-row	Multiple value lists
Column list	Optional but recommended
Omitted columns	Use defaults or NULL
RETURNING	Return generated values (PostgreSQL)
OUTPUT	SQL Server equivalent
LAST_INSERT_ID()	MySQL equivalent
INSERT ... SELECT	Insert from a query
Constraint violation	Statement fails

Key takeaways:

• The INSERT INTO statement adds one or more rows to a table. The statement specifies the table, the columns, and the values. The column list is optional but recommended because it makes the statement resilient to table changes .
• The multi-row INSERT statement inserts multiple rows in a single operation. The value lists are separated by commas. The multi-row form is faster than multiple single-row statements because it reduces round trips and parses the statement once .
• Omitted columns use their defaults or NULL. If the column has a DEFAULT constraint, the default value is used. If the column is nullable, NULL is used. If the column is NOT NULL and has no default, the statement fails .
• The RETURNING clause returns the values of the inserted row. PostgreSQL, SQL Server, and Oracle support it. MySQL uses LAST_INSERT_ID() for auto-increment columns. The clause is useful when the application needs the generated values .
• The INSERT ... SELECT statement inserts the result of a query. The column list of the INSERT must match the column list of the SELECT in number and in type. The statement is useful for copying data between tables .
• The database enforces the constraints on every insert. Primary keys, unique constraints, NOT NULL constraints, CHECK constraints, and foreign keys are all checked. If any constraint is violated, the statement fails and no rows are inserted .
• The INSERT statement is part of a transaction. If the transaction is rolled back, the inserted rows are removed. If the transaction is committed, the rows are permanent.

Remember: The INSERT INTO statement adds rows. The column list specifies the target columns. The value list specifies the values. The order must match. The multi-row form inserts multiple rows in one statement. The omitted columns use defaults or NULL. The RETURNING clause returns the generated values. The INSERT ... SELECT pattern copies rows from a query. The constraints are enforced. The transaction determines the durability.

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!