Skip to content
Tutorials Center
My learning

Topics

Lesson 57 of 125 ยท Changing Data

INSERT

Learn SQL INSERT INTO step by step. Add one or many rows, rely on column defaults, copy rows with INSERT ... SELECT and read generated ids with RETURNING.

So far every query in this course has only read data. Real applications also need to change it: a customer signs up, a product goes on sale, an order is placed. The statements that change data are often called DML (data manipulation language), and the first one you need is INSERT, which adds new rows to a table.

INSERT looks simple, but there are a few habits that separate fragile inserts from robust ones: always naming the columns, letting the database fill in defaults and generated ids, inserting many rows at once, and copying rows from a query. You'll practice all of them here on the shop database. Every run starts from a fresh copy, so add as much as you like.

The basic form

An INSERT names the table, lists the columns you are filling, and then gives the values in the same order:

SQL
INSERT INTO table_name (column_1, column_2, column_3)
VALUES (value_1, value_2, value_3);

The shop database has six product categories. Add a seventh, then read the table back to see it:

SQL Insert one row ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The runner reports OK, 1 row affected. for the INSERT, and the SELECT shows the new row at the end. Values follow the same rules as in a WHERE clause: text goes in single quotes, numbers don't, and NULL is written without quotes.

Always list the columns

SQL also lets you leave out the column list, in which case you must supply a value for every column, in the order the table defines them:

SQL
-- Works, but fragile
INSERT INTO categories
VALUES (8, 'Pets');

This saves a few keystrokes today and breaks tomorrow. If someone later adds a column to categories, the statement fails because the value count no longer matches. Worse, if two columns of the same type are swapped in a schema change, the insert keeps working and silently puts data in the wrong place. Naming the columns makes each insert self-documenting and immune to column order. Use the short form only for quick experiments.

Inserting several rows at once

One INSERT can add many rows. Separate each row's parentheses with a comma:

SQL Insert several rows ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The runner reports OK, 3 rows affected. Multi-row inserts are faster than one statement per row, because the database parses and plans the statement once. They also succeed or fail as a unit: if any row breaks a rule, none of them are added. You'll see that in action in a moment.

Loading thousands of rows? Send them in batches of a few hundred rows per statement, inside a transaction. That's usually dramatically faster than thousands of single-row inserts, each committed on its own. The Importing and Exporting Data lesson covers bulk loading in more depth.

Leaving columns out: defaults and generated ids

You don't have to fill every column. Any column you leave out of the list gets its default value, or NULL if it has no default. Two kinds of columns are commonly left out on purpose:

  • Generated ids. In SQLite, a column declared INTEGER PRIMARY KEY gets the next free number automatically. Other databases use IDENTITY, SERIAL or AUTO_INCREMENT columns for the same job; see Auto-Increment and Identity Columns.
  • Columns with a DEFAULT. In the shop schema, products.stock is declared DEFAULT 0, and orders.shipping is DEFAULT 0 too.

This example builds a small tasks table, then inserts rows that leave different columns out:

SQL Defaults and generated ids

Press Run (or Ctrl + Enter) to see the result here.

Every row got an id (1, 2 and 3) without you choosing one. The first row picked up both defaults ('todo' and 3) and a NULL due date, because due_on has no default.

If a column is NOT NULL and has no default, you must provide it. Leaving title out of the inserts above fails with NOT NULL constraint failed: tasks.title.

DEFAULT VALUES and the DEFAULT keyword

To insert a row made entirely of defaults, use DEFAULT VALUES instead of a column list:

SQL
INSERT INTO visits DEFAULT VALUES;

PostgreSQL, MySQL and SQL Server also accept the keyword DEFAULT in place of a value, such as VALUES (DEFAULT, 'Fix the login bug'). SQLite doesn't support that; simply leave the column out of the list instead, which works everywhere.

Copying rows with INSERT ... SELECT

Instead of VALUES, an insert can take its rows from a query. Every row the SELECT returns becomes a new row in the target table. The columns are matched by position, so the query must return the same number of columns, in the same order as the column list.

This is the standard way to fill a summary table, archive old data or copy rows between tables. Here you build a vip_customers table from customers who placed at least five orders:

SQL INSERT ... SELECT ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The column names in the SELECT don't matter, only their positions. Any query works here: joins, GROUP BY, CTEs and so on.

Create and fill in one step. Many databases can also create a brand-new table straight from a query: CREATE TABLE vip AS SELECT ... in SQLite, PostgreSQL and MySQL, or SELECT ... INTO vip FROM ... in SQL Server. The new table copies the column names but usually not the keys or constraints, so INSERT ... SELECT into a table you defined yourself gives you more control. CREATE TABLE covers both.

Getting generated values back with RETURNING

When the database generates the id, your application usually needs to know it, for example to insert the order's items right after the order. Adding RETURNING to an insert makes it return the inserted rows like a SELECT would. You can list any columns, including defaults and expressions:

SQL INSERT ... RETURNING ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The new orders got ids 121 and 122, the next numbers after the highest existing id (120), and shipping came back as 0 from its default. Because the statement now returns columns, the runner shows a table instead of a row count.

Support for RETURNING varies, and each database has its own way of reading the last generated id:

Database Return inserted rows Last generated id
SQLite (3.35+) RETURNING id last_insert_rowid()
PostgreSQL RETURNING id lastval() / currval('seq')
MariaDB (10.5+) RETURNING id LAST_INSERT_ID()
MySQL not supported LAST_INSERT_ID()
SQL Server OUTPUT inserted.id SCOPE_IDENTITY()
Oracle RETURNING id INTO :v (PL/SQL) sequence .CURRVAL
SQL
-- SQL Server: OUTPUT goes before VALUES
INSERT INTO orders (customer_id, ordered_at, status)
OUTPUT inserted.id
VALUES (7, '2026-01-10 09:15', 'pending');

Prefer RETURNING (or OUTPUT) when you have it. It works for multi-row inserts and can't accidentally pick up an id generated by a different statement.

When an insert fails

The database refuses any row that breaks a rule on the table: a NOT NULL column left empty, a duplicate value in a UNIQUE or primary key column, or a CHECK that evaluates to false. In the shop schema, customers.email is UNIQUE and products.price has CHECK (price >= 0).

The next example fails on purpose. The second row reuses an email that customer 1 already has:

SQL A failing insert ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The run stops with Error: UNIQUE constraint failed: customers.email, and the SELECT never runs. Change the second email to something new and run it again: now both rows go in and the count is 42. If you query the table after the failed version, you'd find that Ines wasn't added either: a failing statement undoes all of its own changes. Grouping several statements into an all-or-nothing unit is the job of transactions.

Sometimes a duplicate isn't an error but something you expect, such as a sign-up form submitted twice. For that, SQL has upserts: INSERT ... ON CONFLICT DO NOTHING skips the duplicate, and ON CONFLICT DO UPDATE updates the existing row instead. They get their own lesson: Upserts with ON CONFLICT.

Never build an INSERT by gluing user input into a string. Code like "INSERT ... VALUES ('" + name + "')" lets a crafted name rewrite your statement. Applications must pass values as parameters (placeholders like ? or $1). See SQL Injection and Secure Queries.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why should you list the columns in an INSERT, even when you fill all of them?

    Without a column list the values are matched to the table's current column order, so schema changes break the insert or silently misplace data.
  2. 2. A table is CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT, level INTEGER DEFAULT 1). What does INSERT INTO t (name) VALUES ('Ana'); store in level?

    An omitted column gets its default. level has DEFAULT 1, so the new row stores 1, and id is generated automatically.
  3. 3. In INSERT INTO archive (a, b) SELECT x, y FROM live, how are the selected columns matched to a and b?

    INSERT ... SELECT matches columns by position. Aliases in the SELECT are ignored.
  4. 4. A three-row INSERT fails because the third row violates a UNIQUE constraint. How many rows were added?

    A statement is all-or-nothing. When one row fails, the database undoes the rows that statement already inserted.
  5. 5. Which clause makes an INSERT return the generated id in SQLite and PostgreSQL?

    RETURNING is supported by SQLite 3.35+, PostgreSQL and MariaDB. SQL Server uses OUTPUT inserted.id, and MySQL offers LAST_INSERT_ID().

Summary

  • INSERT INTO table (columns) VALUES (...) adds rows. Always list the columns.
  • One statement can insert many rows: VALUES (...), (...), (...). It's faster and all-or-nothing.
  • Omitted columns get their DEFAULT, or NULL; INTEGER PRIMARY KEY columns get the next id automatically.
  • INSERT INTO ... SELECT ... copies query results into a table, matching columns by position.
  • RETURNING hands back the inserted rows, including generated ids and defaults (OUTPUT in SQL Server).
  • A row that breaks a NOT NULL, UNIQUE, PRIMARY KEY or CHECK rule makes the whole statement fail.

Next: UPDATE, where you'll change rows that already exist.