Skip to content
Tutorials Center
My learning

Topics

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.

SQL
INSERT INTO categories (name)
VALUES ('Garden');

Try it

SQL Inserting one row and several rows ยท database: shop

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

SQL Defaults and a rejected row (fails on purpose) ยท database: shop

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

Syntax

SQL
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
-- 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 KEY out of the column list and the database assigns it. SQLite then reports it through last_insert_rowid() or RETURNING.
  • 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 VALUES by gluing user input into SQL text; use parameters. See SQL injection.