beginner

INSERT Statement in SQL

By DataPlayArena Technical Review BoardUpdated 2026-07-208 min readRuntime Verified

Overview

The INSERT statement is the Data Manipulation Language (DML) command used to add new rows of data into a database table. In real-world data engineering and backend development, single-row INSERT statements are rarely used in production due to network round-trip overhead. High-throughput pipelines rely on batch multi-row inserts, INSERT INTO SELECT pipelines, Upserts (ON CONFLICT / ON DUPLICATE KEY UPDATE), and atomic transaction management.

What You Will Learn

In this lesson, you will master:
  • Syntax Standards: Explicit column specification vs positional implicit insertion.
  • Batch Multi-Row Inserts: Maximizing throughput by batching thousands of records per statement.
  • INSERT INTO ... SELECT: Populating target tables directly from existing query datasets without round-trips.
  • Upserts (ON CONFLICT / MERGE): Handling primary key and unique constraint collisions idempotently.
  • The RETURNING Clause: Capturing generated auto-incrementing IDs immediately upon insert.

Basic & Multi-Row Batch Inserts

Always explicitly list destination column names. Omitting column names (INSERT INTO table VALUES (...)) makes code fragile when table schemas evolve with new columns or altered column order:

sql
-- Single-Row Insertion (Explicit Columns)
INSERT INTO customers (full_name, email, is_active)
VALUES ('Alice Smith', 'alice@example.com', TRUE);

-- Multi-Row Batch Insertion (High Performance)
-- Inserts 3 rows in a single network round-trip
INSERT INTO customers (full_name, email, is_active)
VALUES 
    ('Bob Jones', 'bob@example.com', TRUE),
    ('Charlie Brown', 'charlie@example.com', FALSE),
    ('Diana Prince', 'diana@example.com', TRUE);
Batch Sizing Best Practice

When ingesting bulk data into PostgreSQL or MySQL, batch statements into 500 to 2,000 rows per transaction. Sending 100,000 rows in a single query can exceed parameter buffer limits (max_allowed_packet in MySQL), while single-row inserts are 50x slower due to network and fsync latency.


INSERT INTO ... SELECT (Data Migration & ETL)

To copy or transform records between tables without pulling data into your application layer:

sql
-- Archive completed orders older than 1 year into a cold storage table
INSERT INTO archived_orders (order_id, customer_id, order_total, archived_at)
SELECT 
    order_id, 
    customer_id, 
    order_total, 
    CURRENT_TIMESTAMP
FROM active_orders
WHERE status = 'COMPLETED' 
  AND ordered_at < CURRENT_DATE - INTERVAL '1 year';

Upserts: Idempotent Insertion with ON CONFLICT

In distributed pipelines and webhooks, retry attempts often re-send identical data. Without handling duplicates, unique constraints raise errors and abort the transaction. An Upsert (Update or Insert) solves this:

PostgreSQL & SQLite Syntax: ON CONFLICT

sql
INSERT INTO user_preferences (user_id, theme, notifications_enabled, updated_at)
VALUES (101, 'dark', TRUE, CURRENT_TIMESTAMP)
ON CONFLICT (user_id) -- Must match a UNIQUE or PRIMARY KEY constraint
DO UPDATE SET
    theme = EXCLUDED.theme,
    notifications_enabled = EXCLUDED.notifications_enabled,
    updated_at = CURRENT_TIMESTAMP;

-- Alternative: Silently ignore existing duplicates
INSERT INTO audit_log (log_id, message)
VALUES ('msg-9921', 'User logged in')
ON CONFLICT (log_id) DO NOTHING;

MySQL Syntax: ON DUPLICATE KEY UPDATE

sql
INSERT INTO user_preferences (user_id, theme, notifications_enabled)
VALUES (101, 'dark', TRUE)
ON DUPLICATE KEY UPDATE
    theme = VALUES(theme),
    notifications_enabled = VALUES(notifications_enabled);

Capturing Generated Keys: RETURNING (PostgreSQL)

When inserting rows with auto-generated identity primary keys or default timestamps, PostgreSQL allows you to return generated values directly without executing a subsequent SELECT:

sql
INSERT INTO orders (customer_id, order_total)
VALUES (42, 189.99)
RETURNING order_id, ordered_at;

-- Returns:
-- order_id | ordered_at
-- ---------+----------------------------
--     1001 | 2026-07-20 14:32:00.123456

Transaction Management & Atomicity

In mission-critical banking and e-commerce systems, related inserts must succeed or fail as a single atomic unit:

sql
BEGIN TRANSACTION;

-- Insert master order record
INSERT INTO orders (order_id, customer_id, total_amount)
VALUES (5001, 88, 150.00);

-- Insert child line items
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES 
    (5001, 10, 1, 100.00),
    (5001, 12, 1, 50.00);

-- If everything succeeds, commit permanently
COMMIT;

-- If an error occurs midway, all changes are rolled back automatically!
-- ROLLBACK;

Common Mistakes & Gotchas

  • Omitting the Column List: Writing INSERT INTO users VALUES (1, 'Alice') causes immediate SQL failure as soon as a DBA adds a new column to users with an ALTER TABLE.
  • Mixing Up Transaction Scopes: In PostgreSQL, an error in any single INSERT within an uncommitted transaction aborts the entire transaction block until a ROLLBACK is issued.

Interview Perspective

SQL Interview Question: What is an Upsert, and how do PostgreSQL and MySQL implement it differently?

Key Talking Points for Candidates:

  1. Concept: An Upsert allows an application to insert a row if it does not already exist, or update the existing row if a unique/primary key collision occurs, achieving idempotency.
  2. PostgreSQL: Implements standard ON CONFLICT (target_column) DO UPDATE SET ... utilizing the pseudo-table EXCLUDED to reference values proposed for insertion.
  3. MySQL: Implements ON DUPLICATE KEY UPDATE column = VALUES(column) or modern alias syntax.

Interactive Practice Challenge

Challenge: Idempotent Daily Metric Counter

You have a table daily_pageviews with columns (metric_date DATE, page_path VARCHAR, view_count INT) with a composite primary key on (metric_date, page_path). Write an INSERT statement that records 1 view for ('2026-07-20', '/spark/learn'). If the row already exists, increment view_count by 1.

DPA

DataPlayArena Technical Editorial Board

Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.

Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.