SQL INSERT

What is the SQL INSERT statement?

Adds one or more new rows to a table or view.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL INSERT: Supported by PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, BigQuery, Redshift, DB2, and all ANSI-compliant databases.

SQL INSERT Full Explanation

SQL INSERT is a core Data Manipulation Language (DML) command that appends data to an existing table or updatable view. It can insert a single row, multiple rows, or the result set of a subquery. INSERT can work with explicit column lists, default values, or expressions. Some dialects extend INSERT with clauses like RETURNING (PostgreSQL), ON CONFLICT/ON DUPLICATE KEY (PostgreSQL, SQLite, MySQL), or OUTPUT (SQL Server) to make row-level feedback or upsert behavior possible. Because INSERT permanently changes data, it should be wrapped in a transaction when atomicity or rollback capability is required. Attempting to insert data that violates constraints (primary key, uniqueness, foreign key, NOT NULL, or check constraints) will raise an error unless conflict-handling syntax is used.

SQL INSERT Syntax

-- Insert one row with explicit column list
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

-- Insert multiple rows
INSERT INTO table_name (column1, column2)
VALUES (value1a, value2a),
       (value1b, value2b);

-- Insert from a subquery
INSERT INTO target_table (column1, column2)
SELECT column_a, column_b
FROM source_table
WHERE column_c > 100;

-- Dialect extension: return generated id (PostgreSQL)
INSERT INTO users (email) VALUES ('test@example.com')
RETURNING id;

SQL INSERT Parameters

table_name (identifier) - Target table or updatable view.
column_list (identifier) - Optional comma-separated list of target columns.
VALUES (keyword) - Introduces literal value tuples to insert.
value_list (expression) - One or more comma-separated expressions per row.
subquery (query) - SELECT statement supplying rows for insertion.
DEFAULT (keyword) - Inserts default column value defined in schema.
RETURNING (clause) - Optional dialect clause that returns inserted rows.
ON CONFLICT (clause) - Optional dialect clause for upsert/ignore logic.

Example Queries Using SQL INSERT

-- 1. Insert a single product
INSERT INTO products (sku, name, price)
VALUES ('P1001', 'USB-C Cable', 9.99);

-- 2. Bulk insert two rows
INSERT INTO products (sku, name, price)
VALUES ('P1002', 'Laptop Stand', 29.99),
       ('P1003', 'Wireless Mouse', 19.99);

-- 3. Copy active customers into VIP table
INSERT INTO vip_customers (customer_id, joined_at)
SELECT id, CURRENT_DATE
FROM customers
WHERE total_spend > 1000;

-- 4. PostgreSQL: get generated id back
INSERT INTO users (email, password_hash)
VALUES ('alice@example.com', '...')
RETURNING id;

Expected Output Using SQL INSERT

New rows are appended to the target table
Autogenerated columns (identity, SERIAL, AUTO_INCREMENT) receive system-generated values
If a RETURNING/OUTPUT clause is used, the database returns the requested column values
On success, the transaction can be committed; on failure, it rolls back or raises an error

Use Cases with SQL INSERT

Seeding reference data during application setup.
Ingesting log or event records in batch jobs.
Copying transformed data into reporting tables.
Creating new user records from a signup form.
Populating junction tables in many-to-many relationships.

Common Mistakes with SQL INSERT

Omitting a column in the column list while still providing a value, causing a column count mismatch error.
Inserting duplicate primary-key values, leading to constraint violations.
Forgetting to wrap bulk inserts in a transaction, which hurts performance and atomicity.
Relying on implicit column order, which breaks when the table schema changes.
Mixing data types (e.g., inserting a string into an integer column) and getting type-conversion errors.

First Introduced In

ANSI SQL-86

Frequently Asked Questions

What is the difference between INSERT and UPSERT?

INSERT only adds new rows. UPSERT (INSERT ... ON CONFLICT/ON DUPLICATE KEY) inserts or updates depending on whether a conflict occurs.

How do I insert default values?

Omit the column in the column list or explicitly use DEFAULT in the VALUES list or the DEFAULT VALUES clause, if supported.

Why is my INSERT slow?

Large batches without indexing, unnecessary triggers, or row-by-row commits can degrade performance. Use bulk inserts inside a single transaction and disable indexes if possible.

Can I insert into a view?

Yes, but only if the view is updatable and you have the required permissions. Some databases restrict inserts when the view contains joins, aggregates, or DISTINCT.