Lesson 16 of 61

5 mins read

SQL INSERT INTO: Single & Multi-Row Insertions

In Plain English

The `INSERT INTO` statement creates and adds new rows of data into an existing database table. You can specify exact target column lists or insert multiple rows in a single batch statement.

The Mental Model

"`INSERT INTO` is like submitting a new blank registration form: you fill in each required box (name, email, age) and place it into the filing drawer."

Deep Dive: How It Works

Explicit Column Specification: `INSERT INTO users (name, email) VALUES ('Alex', '[email protected]')`. Explicitly naming columns protects scripts from breaking when new nullable/default columns are added later.

Multi-Row Batch Inserts: `INSERT INTO table (col1, col2) VALUES (a, b), (c, d), (e, f);` is up to 100x faster than executing individual single-row insert statements.

DEFAULT Values: Omitting columns with `DEFAULT` constraints or `AUTO_INCREMENT` keys automatically populates them with their default initial values.

Syntax Blueprint

INSERT INTO table_name (column1, column2)
VALUES
  (value1, value2),
  (value3, value4);

Always name target columns explicitly and batch multiple value rows together.

Core Rules to Remember

Explicit Columns Rule: Never omit column names in production (`INSERT INTO table VALUES (...)` is fragile).

Batch Performance: Multi-row inserts reduce network round trips and transaction commit overhead.

Auto-Generated IDs: Primary keys with AUTOINCREMENT or SERIAL are populated automatically.

Common Beginner Traps & How to Fix Them

Mistake:Mismatching the number or order of column names with the values list.

Why it happens: Column 1 maps to Value 1; if mismatched, type errors or data corruption occur.

How to fix: Ensure column list and value tuples have exact 1:1 positional alignment.

Pro Tip: In PostgreSQL and modern SQLite, append `RETURNING id, created_at` to an INSERT statement to return newly generated primary key IDs immediately.

Live Interactive Example

Hit Run Code to see it live

Batch Inserting Multiple Rows with Defaults

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: Three rows were inserted in a single batch, automatically inheriting in_stock=1.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Insert a Record into a Table

Insert a row with `(id, name, score)` equal to `(1, 'Alex', 100)` into `contestants`.
Select all rows from `contestants`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.