SQL INSERT INTO

The INSERT INTO statement is how you add new rows of data into an existing table. Every time an application creates a user account, records an order, or logs an event, there is very likely an INSERT INTO statement running behind the scenes. It looks simple at first glance, but understanding exactly how the database engine validates, orders, and commits new rows will save you from subtle bugs later on.

Overview: How INSERT INTO Works

INSERT INTO is a Data Manipulation Language (DML) statement, not a Data Definition Language (DDL) one — it adds rows to a table that must already exist, it does not create or alter the table itself. When you run an INSERT, the engine performs several steps for every row you supply:

First, it resolves the target table and, if you gave a column list, maps each value to the correct column by position. Second, for each row, it checks every constraint defined on the table: NOT NULL constraints (does every required column have a value?), PRIMARY KEY and UNIQUE constraints (does this value already exist elsewhere in the table or index?), CHECK constraints (does the value satisfy the rule?), and FOREIGN KEY constraints (does the referenced row exist in the parent table?). Third, once a row passes all checks, the engine writes it into the table’s underlying storage and updates every index defined on that table so future lookups, joins, and sorts can find the new row immediately.

In SQLite specifically, every table has a hidden rowid unless it was created with WITHOUT ROWID. If you declare a column as INTEGER PRIMARY KEY, that column becomes an alias for the rowid, and SQLite will auto-generate the next integer for you if you omit it or pass NULL — this is the SQLite equivalent of MySQL’s AUTO_INCREMENT or SQL Server’s IDENTITY.

An INSERT statement is atomic: if any single row in a multi-row insert violates a constraint, the entire statement fails and none of the rows are added (unless you explicitly use conflict-handling clauses, discussed below). This all happens inside an implicit transaction if you have not started one explicitly, so a successful INSERT is immediately visible to subsequent queries.

Syntax

There are a few common forms of INSERT INTO:

-- 1. Explicit column list (recommended)
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

-- 2. All columns, in table order (fragile, not recommended)
INSERT INTO table_name
VALUES (value1, value2, value3);

-- 3. Multiple rows in one statement
INSERT INTO table_name (column1, column2)
VALUES
  (value1a, value2a),
  (value1b, value2b);

-- 4. Insert rows produced by a query
INSERT INTO table_name (column1, column2)
SELECT columnA, columnB FROM other_table WHERE condition;

Clause	Purpose
INSERT INTO table_name	Names the target table, which must already exist.
(column1, column2, ...)	Optional but strongly recommended explicit column list, in the order you will supply values.
VALUES (...)	One or more comma-separated value tuples; each tuple must match the column list in count and compatible type.
SELECT ...	An alternative to VALUES — inserts the rows returned by a query instead of literal values.

Examples

Example 1: A single-row insert with an explicit column list

INSERT INTO employees (id, name, department, salary, hire_date)
VALUES (5, 'Dana Whitfield', 'Engineering', 95000, '2024-03-01');

SELECT * FROM employees WHERE id = 5;

Result:

id	name	department	salary	hire_date
5	Dana Whitfield	Engineering	95000.0	2024-03-01

The column list tells the engine exactly which value goes into which column, so the order of the VALUES tuple must match the order of the column list — not necessarily the table’s physical column order. The follow-up SELECT simply confirms the row landed correctly.

Example 2: Inserting multiple rows in one statement

INSERT INTO products (product_id, product_name, price, category)
VALUES
  (10, 'USB Hub', 19.99, 'Hardware'),
  (11, 'Wireless Mouse', 29.99, 'Hardware'),
  (12, '4K Monitor', 349.99, 'Electronics');

SELECT * FROM products WHERE product_id IN (10, 11, 12) ORDER BY product_id;

Result:

product_id	product_name	price	category
10	USB Hub	19.99	Hardware
11	Wireless Mouse	29.99	Hardware
12	4K Monitor	349.99	Electronics

Multi-row inserts are far more efficient than issuing a separate INSERT per row, because the engine parses the statement once and can batch the underlying writes and index updates, and (depending on your driver) it also means one network round trip instead of many.

Example 3: Omitted columns and DEFAULT values

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author TEXT,
  in_stock INTEGER DEFAULT 1
);

INSERT INTO books (id, title, author) VALUES (1, 'The Pragmatic Programmer', 'David Thomas');
INSERT INTO books (id, title, author, in_stock) VALUES (2, 'Clean Code', 'Robert C. Martin', 0);

SELECT * FROM books;

Result:

id	title	author	in_stock
1	The Pragmatic Programmer	David Thomas	1
2	Clean Code	Robert C. Martin	0

Any column you leave out of the column list gets its DEFAULT value (or NULL if no default is defined and the column allows nulls). The first insert omits in_stock, so it falls back to the table’s default of 1; the second insert supplies it explicitly as 0.

Example 4: Inserting rows produced by a SELECT query

CREATE TABLE staging_orders (
  order_id INTEGER,
  customer_id INTEGER,
  amount REAL,
  status TEXT
);

INSERT INTO staging_orders (order_id, customer_id, amount, status)
VALUES
  (1, 1, 250.00, 'approved'),
  (2, 2, 80.00, 'pending'),
  (3, 1, 120.00, 'approved');

CREATE TABLE approved_orders (
  order_id INTEGER,
  customer_id INTEGER,
  amount REAL
);

INSERT INTO approved_orders (order_id, customer_id, amount)
SELECT order_id, customer_id, amount
FROM staging_orders
WHERE status = 'approved';

SELECT * FROM approved_orders ORDER BY order_id;

Result:

order_id	customer_id	amount
1	1	250.0
3	1	120.0

This is INSERT INTO ... SELECT, commonly used to copy, filter, or archive data between tables without pulling it into application code first. Here only the rows with status = 'approved' are copied into approved_orders.

How It Works Step by Step (Under the Hood)

For a plain INSERT INTO ... VALUES statement, the engine works through each value tuple in order: validate column count matches, coerce/check types against column affinity, check constraints, write the row, update indexes, move to the next tuple. If any tuple fails, SQLite aborts the whole statement (no partial insert survives) unless you have overridden this with a conflict-resolution clause such as INSERT OR IGNORE or INSERT OR REPLACE (SQLite-specific; the portable equivalents are MySQL’s INSERT IGNORE / ON DUPLICATE KEY UPDATE and PostgreSQL’s ON CONFLICT ... DO UPDATE, both of which implement "upsert" behavior).

For INSERT INTO ... SELECT, the engine first fully evaluates the SELECT using its own logical processing order — FROM/JOIN, then WHERE, then GROUP BY, then HAVING, then the SELECT column list, then ORDER BY, then any LIMIT — to produce a result set. Only once that result set is determined does the engine begin feeding those rows, one at a time, through the same constraint-checking and index-updating pipeline used for a literal VALUES insert. This means the source query can reference the target table too, as long as it does not create infinite recursion, and it means any WHERE filtering on the source happens before a single row is written to the destination.

Common Mistakes

Mistake 1: Column list and value count don’t match

INSERT INTO products (product_id, product_name, price)
VALUES (13, 'Keyboard');

This fails because the column list has three columns but the VALUES tuple only supplies two values. Every value tuple must have exactly as many entries as the column list (or as many as the table has, if you omitted the column list).

INSERT INTO products (product_id, product_name, price, category)
VALUES (13, 'Keyboard', 49.99, 'Hardware');

The corrected version supplies a value for every named column, so it inserts cleanly.

Mistake 2: Forgetting to quote string literals

INSERT INTO customers (customer_id, customer_name, country)
VALUES (4, Acme Corp, USA);

Without quotes, Acme Corp is parsed as two bare identifiers rather than a string, which is a syntax error the moment there’s a space in an unquoted value. String and date literals must always be wrapped in single quotes.

INSERT INTO customers (customer_id, customer_name, country)
VALUES (4, 'Acme Corp', 'USA');

Quoting 'Acme Corp' and 'USA' fixes the syntax and inserts the row correctly.

Best Practices

• Always specify an explicit column list, even when inserting into every column — it protects your statement from breaking silently if someone later adds, removes, or reorders a column.
• Prefer a single multi-row INSERT over many single-row statements when loading bulk data; it is significantly faster because parsing and index maintenance overhead is amortized across rows.
• Wrap large batch inserts in an explicit transaction (BEGIN ... COMMIT) so you get one atomic commit and one disk sync instead of one per row.
• Use parameterized queries (bound parameters) from application code instead of concatenating strings into SQL — this prevents SQL injection and handles quoting/escaping for you automatically.
• Let the database generate surrogate primary keys (INTEGER PRIMARY KEY in SQLite, AUTO_INCREMENT in MySQL, IDENTITY in SQL Server, SERIAL/GENERATED ALWAYS AS IDENTITY in PostgreSQL) rather than computing IDs yourself.
• When copying data with INSERT INTO ... SELECT, run the SELECT alone first to confirm it returns exactly the rows you expect before attaching the INSERT INTO.
• Define NOT NULL, CHECK, and foreign key constraints on your tables so bad data is rejected by the engine at insert time rather than discovered later.

Practice Exercises

Exercise 1: Write an INSERT INTO statement that adds a new employee named ‘Priya Nair’ to the Marketing department with a salary of 72000 and a hire date of ‘2024-06-15’. Use an explicit column list.

Exercise 2: Using a single statement, insert two new customers at once: one from ‘Germany’ and one from ‘Japan’. Pick your own customer_id and customer_name values, and verify the rows with a follow-up SELECT.

Exercise 3: Create a table named high_value_orders with columns order_id, customer_id, and amount. Then use INSERT INTO ... SELECT to copy every row from the orders table where amount is greater than 100 into your new table. What rows do you expect to see?

Summary

• INSERT INTO adds new rows to an existing table; it does not create the table.
• Always prefer an explicit column list so values map to the correct columns regardless of table structure changes.
• You can insert one row, many rows in one statement, or rows produced by a SELECT query (INSERT INTO ... SELECT).
• Columns left out of the column list receive their DEFAULT value or NULL.
• The engine checks NOT NULL, PRIMARY KEY/UNIQUE, CHECK, and foreign key constraints per row, and by default the whole statement fails atomically if any row is invalid.
• For INSERT INTO ... SELECT, the source query is fully evaluated through its own logical order before any row is written to the target table.
• Batch multi-row inserts and wrap bulk loads in a transaction for performance.