SQL INSERT INTO: Adding Rows to a Table

Lesson 8.1 · Reading time: ~7 min · Practice tasks ↓

The SQL INSERT INTO statement adds new rows to a table. In this lesson you'll learn the INSERT syntax, why to always list the columns, how to insert several rows at once, how to copy data with INSERT INTO ... SELECT, and how NULL, DEFAULT and auto-increment columns are filled.

So far, every query in this course has read data with SELECT. With this module you start changing data. INSERT, UPDATE and DELETE form the Data Manipulation Language (DML); INSERT is the one that creates new rows.

The sample databases on SQLtest are read-only, so these statements return a permission error there. The examples use the Sakila schema to show the syntax and the rules that a real database enforces.

INSERT Syntax

With a Column List (Recommended)

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

The values are matched to the columns by position: the first value goes to the first listed column, and so on. Columns that are not listed get their default value or NULL.

Without a Column List

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Here a value must be given for every column, in the order the columns were defined in the table. If someone later adds a column, the statement breaks or, worse, puts values into the wrong columns. Always list the columns in code that is saved and reused.

Inserting One Row

INSERT INTO actor (first_name, last_name)
VALUES ('JOHNNY', 'DEPP');

Result: a new actor. actor_id is filled automatically (auto-increment), and last_update gets the current time from its default value.

Value rules are the same as in WHERE: text and dates in single quotes, numbers without quotes.

Required Columns

A column defined as NOT NULL without a default value must be given a value. In Sakila, customer.create_date is such a column:

-- Error: Field 'create_date' doesn't have a default value
INSERT INTO customer (store_id, first_name, last_name, email, address_id)
VALUES (1, 'ALICE', 'JOHNSON', 'alice.j@example.com', 5);

-- Correct: the required column has a value
INSERT INTO customer (store_id, first_name, last_name, email, address_id, create_date)
VALUES (1, 'ALICE', 'JOHNSON', 'alice.j@example.com', 5, NOW());

Result: a new customer of store 1. customer_id is generated, active gets its default value 1.

Values must also satisfy foreign keys: store_id = 1 and address_id = 5 must exist in the store and address tables, otherwise the database rejects the row.

Inserting Several Rows

Several value lists separated by commas insert several rows in one statement:

INSERT INTO actor (first_name, last_name)
VALUES ('TOM', 'HANKS'),
       ('MERYL', 'STREEP'),
       ('LEONARDO', 'DICAPRIO');

Result: three new actors.

One multi-row statement is much faster than many single-row ones: the database parses it once and writes the changes in one go. It is the standard way to load data in batches.

NULL and DEFAULT Values

NULL writes "no value" into a column that allows it:

INSERT INTO customer (store_id, first_name, last_name, email, address_id, create_date)
VALUES (1, 'BOB', 'SMITH', NULL, 5, NOW());

Result: a customer without an email address.

Omitting a column fills it with its default value, or NULL if there is no default.

DEFAULT requests the default value explicitly, which is handy when every column is listed:

INSERT INTO actor (actor_id, first_name, last_name, last_update)
VALUES (DEFAULT, 'CATE', 'BLANCHETT', DEFAULT);

Result: actor_id gets the next auto-increment number, last_update the current time.

INSERT INTO ... SELECT

Instead of VALUES, an INSERT can take its rows from a query. This is how data is copied between tables, archived or loaded into report tables:

INSERT INTO target_table (column1, column2, column3)
SELECT source_column1, source_column2, source_column3
FROM source_table
WHERE condition;

The SELECT list can combine columns, calculations and constants. For example, if a customer_archive table exists, inactive customers can be copied into it:

INSERT INTO customer_archive (customer_id, full_name, archived_at, status_note)
SELECT customer_id,
       CONCAT(first_name, ' ', last_name),
       CURRENT_DATE,
       'Auto-archived'
FROM customer
WHERE active = 0;

Result: 15 rows, one per inactive customer, with the full name, today's date and a fixed note.

The number and order of the SELECT columns must match the column list of the INSERT. Column names don't have to match; positions do.

Common INSERT Errors

Key Takeaways:

• INSERT INTO table (columns) VALUES (values) adds a row; values match columns by position.
• Always list the columns: the statement then survives changes to the table.
• Required NOT NULL columns without defaults must get a value; foreign keys must point to existing rows.
• One statement can insert many rows: VALUES (...), (...), (...).
• INSERT INTO ... SELECT inserts the result of a query.
• Omitted columns get their default or NULL; the DEFAULT keyword requests the default explicitly.

Frequently Asked Questions

How do I insert multiple rows in SQL?

List several value groups after VALUES, separated by commas: INSERT INTO actor (first_name, last_name) VALUES ('TOM', 'HANKS'), ('MERYL', 'STREEP');. This works in MySQL, MariaDB, PostgreSQL, SQL Server and SQLite.

How do I copy rows from one table to another?

Use INSERT INTO target (columns) SELECT columns FROM source WHERE condition;. The SELECT must return the same number of columns in the same order as the target column list.

What happens to columns I don't list in INSERT?

They get their default value. An auto-increment key gets the next number. If a column has no default, it gets NULL, or the statement fails if the column is NOT NULL.