SQL guide · Changing data

SQL INSERT INTO: Add Rows With VALUES

The INSERT INTO statement adds new rows to a table. In its most common form you name the table, list the columns you want to fill, and provide the matching values in a VALUES clause. Each set of values in parentheses becomes one new row.

Listing the column names is strongly recommended. If you leave them out, the values must be supplied for every column in the exact order the table was defined, which breaks the moment someone adds a column or changes the order. With an explicit column list, any column you skip receives its default value, or NULL if it has no default, and auto-generated keys are filled in for you.

Most databases accept several rows in a single VALUES list, which is faster than running many separate statements because the database parses the statement once and can batch the writes. The statement fails if a value violates a constraint, such as a duplicate primary key, a missing NOT NULL value, or a foreign key that points at a row that does not exist.

Basic syntax

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

-- several rows at once
INSERT INTO table_name (column1, column2, column3)
VALUES (v1, v2, v3),
       (v4, v5, v6);

The column list and each value list must have the same number of items, and values are matched to columns by position. Text and date values are written in single quotes, numbers are not. Omitted columns take their default value or NULL.

SQL INSERT INTO examples

Insert a single row

This adds one customer. The customer_id column is left out because it is generated automatically, and created_at falls back to its default value.

INSERT INTO customers (customer_name, email, country)
VALUES ('Dana Ortiz', 'dana.ortiz@example.com', 'US');

Insert several rows in one statement

Separating value lists with commas inserts multiple rows at once. If any row violates a constraint, the whole statement fails and none of the rows are added.

INSERT INTO products (product_name, category, unit_price)
VALUES ('Desk Lamp', 'Home', 24.99),
       ('Notebook', 'Office', 3.50),
       ('USB Cable', 'Electronics', 7.25);

Use defaults explicitly

The DEFAULT keyword asks the database to use the column default, which is helpful for status columns. Columns you do not mention at all are also filled with their defaults.

INSERT INTO orders (customer_id, order_date, status)
VALUES (42, '2026-10-26', DEFAULT);

Get the generated ID back

Many databases can return the inserted row. PostgreSQL uses RETURNING, which lets you read the new key without a second query.

INSERT INTO customers (customer_name, email)
VALUES ('Priya Nair', 'priya.nair@example.com')
RETURNING customer_id;

Where SQL dialects differ

PostgreSQL: Supports multi-row VALUES, RETURNING to read back inserted rows, DEFAULT VALUES, and ON CONFLICT DO NOTHING or DO UPDATE for upserts. Identity columns use GENERATED ... AS IDENTITY.

MySQL: Supports multi-row VALUES, AUTO_INCREMENT keys read back with LAST_INSERT_ID(), INSERT IGNORE, and ON DUPLICATE KEY UPDATE for upserts. It has no RETURNING clause.

SQL Server: Supports multi-row VALUES with up to 1,000 rows per statement. Use OUTPUT inserted.column to return values, and SCOPE_IDENTITY() for the last IDENTITY value. Inserting into an identity column needs SET IDENTITY_INSERT ON.

SQLite: Supports multi-row VALUES and INSERT OR REPLACE or INSERT OR IGNORE. An INTEGER PRIMARY KEY column is filled automatically, and RETURNING is available since version 3.35.0.

Oracle: Multi-row VALUES lists are supported only from 23c. Older versions use INSERT ALL INTO ... SELECT 1 FROM dual, and sequences or identity columns generate keys.

Common mistakes

  • Omitting the column list. The statement then depends on table column order and breaks when the table changes.
  • Supplying the wrong number of values. The count of values must match the count of listed columns, otherwise the statement fails.
  • Forgetting quotes around text or dates, or failing to escape an apostrophe inside a string. Double the quote, as in 'O''Brien', or use parameters.
  • Inserting a duplicate key. A repeated primary key or unique value raises an error, so check first or use the upsert syntax for your database.
  • Building the statement by pasting user input into the SQL text. Use parameterised queries to avoid SQL injection and quoting bugs.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator