Querydrill

Basics Updated November 2026

INSERT INTO

INSERT INTO table (col1, col2) VALUES (v1, v2) adds one row; list several (v1, v2), (v3, v4) groups to add many at once. INSERT INTO table (cols) SELECT ... copies rows from a query. Columns you leave out get their default or NULL, and auto-increment keys fill themselves in.

Syntax

INSERT INTO table (column1, column2) VALUES (value1, value2) [, (value3, value4)];

Examples

INSERT INTO customers (name, country) VALUES ('Noor', 'Aruba');

Result: 1 row inserted; id is generated automatically

INSERT INTO products (name, price) VALUES
  ('Mug', 8.50),
  ('Cap', 15.00),
  ('Pen', 1.20);

Result: 3 rows inserted

INSERT INTO archived_orders (id, customer_id, amount)
SELECT id, customer_id, amount FROM orders WHERE order_date < '2024-01-01';

Result: Copies old orders into the archive table

Common mistakes

  • Value count does not match column count (table has 4 columns but 3 values were supplied); list the columns explicitly so the order and count are clear.
  • Inserting a duplicate primary key or unique value raises UNIQUE constraint failed; use INSERT ... ON CONFLICT DO NOTHING / DO UPDATE (upsert) in SQLite and PostgreSQL.
  • Quoting numbers ('15.00') is tolerated by SQLite but can bite in PostgreSQL for the wrong types; write numbers without quotes and strings with single quotes.

Practice it

The drills on Querydrill check your answer on live data, free, no account needed.

Start practicing

Questions

How do I insert multiple rows at once?

Put several parenthesized value groups after VALUES, separated by commas. One multi-row INSERT is much faster than many single inserts.

How do I get the id of the row I just inserted?

Add RETURNING id (PostgreSQL, SQLite 3.35+). In older SQLite call SELECT last_insert_rowid() right after the insert.

How do I insert or update if the row exists (upsert)?

INSERT INTO t (id, qty) VALUES (1, 5) ON CONFLICT (id) DO UPDATE SET qty = excluded.qty. Same syntax in SQLite and PostgreSQL.

Related

  • UPDATEUPDATE table SET column = value WHERE condition changes matching rows. Several columns, compute from the current value, update from another table, stay safe.
  • DELETEDELETE FROM table WHERE condition removes matching rows. Delete by id, by subquery, duplicates, everything in a table, and DELETE versus TRUNCATE.
  • CREATE TABLEHow to create a table with column types, NOT NULL, DEFAULT, UNIQUE, PRIMARY KEY and FOREIGN KEY in SQLite and PostgreSQL, plus CREATE TABLE AS SELECT.
  • SELECTHow to write a SQL SELECT: choose columns from a table, select everything with *, compute expressions, and combine it with WHERE, ORDER BY and LIMIT.

Previous: CASTNext: UPDATE