INSERT Statement

Introduction

You have already used INSERT INTO in earlier lessons to add rows while learning about constraints. Now it's time to look at the statement itself in full detail — including how to insert many rows at once, and how to write inserts that are safer and easier to maintain.

The INSERT Syntax

The general form of the INSERT statement names the table, optionally lists the columns you are providing values for, and then supplies those values.

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

The values must be listed in the same order as the columns, and each value's data type must be compatible with its column's type.

Inserting a Single Row

Let's use a products table for this lesson's examples.

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0
);

INSERT INTO products (name, price, stock)
VALUES ('Notebook', 3.50, 100);

SELECT * FROM products;

+----+----------+-------+-------+
| id | name     | price | stock |
+----+----------+-------+-------+
| 1  | Notebook | 3.50  | 100   |
+----+----------+-------+-------+

Inserting Multiple Rows

Instead of writing a separate INSERT statement for every row, MySQL lets you supply several comma-separated value groups in a single statement — this is faster and more efficient than multiple individual inserts.

INSERT INTO products (name, price, stock)
VALUES
    ('Pen', 1.20, 300),
    ('Eraser', 0.75, 150),
    ('Ruler', 2.00, 80);

SELECT * FROM products;

+----+----------+-------+-------+
| id | name     | price | stock |
+----+----------+-------+-------+
| 1  | Notebook | 3.50  | 100   |
| 2  | Pen      | 1.20  | 300   |
| 3  | Eraser   | 0.75  | 150   |
| 4  | Ruler    | 2.00  | 80    |
+----+----------+-------+-------+

All three new rows were added by a single INSERT statement, and MySQL automatically generated their ids (2, 3, and 4) via AUTO_INCREMENT.

Omitting vs Specifying the Column List

MySQL also allows you to skip the column list entirely, as long as you supply a value for every single column in the table, in the table's exact defined order.

-- Works, but relies on knowing the table's exact column order
INSERT INTO products VALUES (5, 'Marker', 1.50, 200);

Missing Required Columns

Recall that "name" and "price" were both defined as NOT NULL with no DEFAULT. If we try to insert a row without providing them, MySQL raises an error rather than guessing a value.

INSERT INTO products (stock) VALUES (50);

ERROR 1364 (HY000): Field 'name' doesn't have a default value

Because "name" has no default and does not allow NULL, MySQL has no valid value to store and rejects the entire statement. This is exactly the kind of protection NOT NULL and DEFAULT give you together — you learned about them in earlier lessons on constraints.

Common Mistakes

Key Takeaways

Summary

The INSERT statement is how new data enters your tables. Beyond the basics, knowing how to insert multiple rows efficiently, and always specifying your column list, will keep your inserts fast, safe, and easy to maintain as your schema evolves.

In this lesson, you inserted single and multiple rows, compared omitting versus specifying columns, and saw the error MySQL raises for a missing required column. Next, you'll learn how to retrieve data back out of your tables with the SELECT statement.