SQL Reference ยท Statements and Clauses
SQL INSERT INTO statement
Reference for SQL INSERT INTO: add one or many rows, list columns explicitly, use DEFAULT VALUES, and see how constraints and dialects affect inserts.
INSERT INTO adds new rows to a table. You name the table, usually list the columns you want to fill, and supply the values with a VALUES list. Columns you leave out receive their DEFAULT value, or NULL if they have none.
Always list the columns. INSERT INTO t VALUES (...) depends on the current column order of the table and breaks when someone adds or reorders a column.
INSERT INTO categories (name) VALUES ('Garden');
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...), (value1, value2, ...); INSERT INTO table_name DEFAULT VALUES;
| Part | Description |
|---|---|
table_name |
The table to add rows to. |
| column list | The columns you supply values for. Omitted columns get their default or NULL. |
VALUES (...) |
One parenthesized row of expressions, in column-list order. Repeat rows with commas. |
DEFAULT VALUES |
Inserts one row made entirely of defaults (not available in MySQL). |
Result: the statement reports how many rows were inserted. If any row violates a NOT NULL, UNIQUE, CHECK or foreign-key constraint, the statement fails and no rows from it are added.
Dialect differences
| Dialect | Notes |
|---|---|
| SQLite | Multi-row VALUES supported. INSERT OR IGNORE and INSERT OR REPLACE are SQLite conflict shortcuts. RETURNING works (3.35+). |
| PostgreSQL | Multi-row VALUES, RETURNING, ON CONFLICT. OVERRIDING SYSTEM VALUE is needed to insert into identity columns that are GENERATED ALWAYS. |
| MySQL | INSERT IGNORE, ON DUPLICATE KEY UPDATE, and the INSERT ... SET col = value form. No DEFAULT VALUES; write INSERT INTO t () VALUES (). |
| SQL Server | Multi-row VALUES is limited to 1000 rows per statement. Use OUTPUT inserted.* instead of RETURNING. IDENTITY columns need SET IDENTITY_INSERT. |
| Oracle | Multi-row VALUES only from 23ai; older versions use INSERT ALL ... SELECT 1 FROM dual. |
-- SQL Server: return the generated key INSERT INTO categories (name) OUTPUT inserted.id VALUES ('Garden');
Usage notes
- List your columns. It documents the statement and survives schema changes.
- One statement, many rows is much faster than many single-row statements, because the database parses once and commits once.
- Strings use single quotes; double a quote to include it:
'O''Brien'. - Generated keys: leave an auto-increment
PRIMARY KEYout of the column list and the database assigns it. SQLite then reports it throughlast_insert_rowid()orRETURNING. - Type mismatches: strict databases reject text for a number column; SQLite's flexible typing may accept it. See type affinity.
- Foreign keys must point at existing parent rows; insert parents first. SQLite enforces this only after
PRAGMA foreign_keys = ON, which is off by default (and off in the runner). - Duplicates: to skip or update rows that collide with a unique key, use an upsert.
- Copying rows from another table uses
INSERT INTO ... SELECT. - Never build
VALUESby gluing user input into SQL text; use parameters. See SQL injection.
Related
INSERT INTO ... SELECT, UPSERT,RETURNING,UPDATE,DELETE- Lessons: INSERT, Constraints, Auto-increment