SQL INSERT INTO

What is SQL INSERT INTO?

INSERT INTO adds one or more new rows to an existing table or updatable 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 INTO: Supported by PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, Redshift, BigQuery, and all ANSI-compliant systems.

SQL INSERT INTO Full Explanation

INSERT INTO is a Data Manipulation Language (DML) statement used to populate tables with data. It can insert a single row, multiple literal rows, or the result set of a subquery. Most dialects also support DEFAULT values, expressions, and optional RETURNING clauses to fetch generated columns (such as serial IDs). When a column list is omitted, values must be supplied for every column in the table in declared order. Failing constraints (NOT NULL, UNIQUE, CHECK, foreign keys) causes the statement to error or, in some dialects, be silently ignored when paired with modifiers like INSERT IGNORE. Some databases support advanced variants such as INSERT ... ON CONFLICT/ON DUPLICATE KEY or INSERT ALL, but the core INSERT INTO syntax remains ANSI-SQL compliant. The operation is transactional: if an INSERT appears inside an open transaction block, changes become permanent only after COMMIT and can be undone with ROLLBACK.

SQL INSERT INTO Syntax

-- Single-row literal insert
INSERT INTO table_name (col1, col2, col3)
VALUES (val1, val2, val3);

-- Multi-row literal insert
INSERT INTO table_name (col1, col2)
VALUES (val1a, val2a),
       (val1b, val2b);

-- Insert from a query
INSERT INTO target_table (col1, col2)
SELECT colA, colB
FROM source_table
WHERE colC > 100;

-- Insert using default values for some columns
INSERT INTO table_name (col1)
VALUES (val1);

-- Insert and return generated columns (PostgreSQL style)
INSERT INTO orders (customer_id, amount)
VALUES (42, 19.99)
RETURNING order_id, created_at;

SQL INSERT INTO Parameters

table_name (identifier) - Destination table or updatable view.
column_list (list) - Optional. Explicit columns receiving data.
VALUES (keyword) - Introduces literal value tuples.
value_tuple (expression) - Comma-separated list of expressions per row.
subquery (query) - SELECT statement whose result rows are inserted.
DEFAULT (keyword) - Fills column with its default value.
RETURNING (clause) - Optional. Returns rows of generated data (dialect specific).

Example Queries Using SQL INSERT INTO

-- 1. Insert a single user
INSERT INTO users (username, email)
VALUES ('ada', 'ada@example.com');

-- 2. Insert multiple products at once
INSERT INTO products (sku, price)
VALUES ('sku_001', 29.99),
       ('sku_002', 15.50),
       ('sku_003', 9.90);

-- 3. Copy recent rows into an archive table
INSERT INTO orders_archive
SELECT *
FROM orders
WHERE order_date < CURRENT_DATE - INTERVAL '90' DAY;

-- 4. Insert with RETURNING to capture the new primary key
INSERT INTO posts (author_id, title)
VALUES (7, 'Hello SQL')
RETURNING post_id;

Expected Output Using SQL INSERT INTO

Each statement adds the specified rows and returns either a row-count message (e
g
, "INSERT 0 3") or, when using RETURNING, a result set containing the requested columns
Constraints are checked; on success, data becomes visible in subsequent queries within the same transaction

Use Cases with SQL INSERT INTO

Seeding reference data during initial database setup
Adding transactional records such as orders, events, or logs
Migrating or copying data between tables or databases
Capturing autogenerated keys immediately after insertion via RETURNING
Batch loading data for analytics when combined with multi-row inserts

Common Mistakes with SQL INSERT INTO

Omitting a NOT NULL column in the column list
Providing a different number of values than columns
Forgetting quotes around string literals
Inserting duplicate keys without handling conflicts (ON CONFLICT or IGNORE)
Misordering values when the column list is left out

First Introduced In

SQL-86 (first ANSI SQL standard)

Frequently Asked Questions

How do I insert a row using default values?

List only the columns you want to set and omit others. The database fills missing columns with their DEFAULT expressions or NULL if allowed.

Can I insert data returned from another query?

Yes. Replace the VALUES clause with a SELECT statement that returns columns compatible with the target column list.

How can I insert and retrieve the new primary key in one round-trip?

Use RETURNING (PostgreSQL, Oracle, SQLite) or OUTPUT (SQL Server). In MySQL call LAST_INSERT_ID() right after the insert.

Is INSERT INTO transactional?

Absolutely. Inside an explicit transaction, you can COMMIT to persist or ROLLBACK to undo the inserted rows.