# SQL INSERT INTO: Add Rows With VALUES

- URL: https://betteranalyst.com/sql/insert-into

SQL guide · Changing data

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

- [SQL INSERT INTO SELECT](https://betteranalyst.com/sql/insert-into-select)
- [SQL UPDATE](https://betteranalyst.com/sql/update)
- [SQL DELETE](https://betteranalyst.com/sql/delete)
- [SQL CREATE TABLE](https://betteranalyst.com/sql/create-table)
- [SQL PRIMARY KEY](https://betteranalyst.com/sql/primary-key)
- [SQL DEFAULT](https://betteranalyst.com/sql/default)

[Browse all SQL guides](https://betteranalyst.com/sql)

## 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](https://betteranalyst.com/ai-sql-query-generator)