INSERT: adding rows

Adding data

SELECT reads; INSERT creates new rows. The basic form names the table, the columns you will fill, and the values in the same order:

INSERT INTO customers (id, first_name, last_name, email, phone, city, country, signup_date)
VALUES (13, 'Mira', 'Joshi', 'mira.joshi@example.com', '+91-98100-00013', 'Pune', 'India', '2024-01-10');

MySQL answers "Query OK, 1 row affected". To see the row, read it back:

SELECT id, first_name, city FROM customers WHERE id = 13;

+----+------------+------+
| id | first_name | city |
+----+------------+------+
| 13 | Mira       | Pune |
+----+------------+------+

The values must be in the same order as the column list, and text and dates need single quotes. NULL is written without quotes.

Always list the columns

SQL also lets you leave the column list out: INSERT INTO customers VALUES (13, 'Mira', ...). Then you must supply a value for every column in the order the table defines. It works until someone adds or reorders a column, after which the statement breaks or, worse, quietly puts values in the wrong place. Naming the columns costs a few extra words and avoids all that.

Leaving columns out

Columns missing from the list get their default. If a column allows NULL and has no default, it becomes NULL:

INSERT INTO customers (id, first_name, last_name, email, city, country, signup_date)
VALUES (14, 'Noah', 'Fischer', 'noah.fischer@example.com', 'Berlin', 'Germany', '2024-01-12');

SELECT id, first_name, phone FROM customers WHERE id = 14;

+----+------------+-------+
| id | first_name | phone |
+----+------------+-------+
| 14 | Noah       | NULL  |
+----+------------+-------+

The phone is NULL because we did not supply one. A column declared NOT NULL with no default cannot be skipped, and the statement fails with an error like Field 'city' doesn't have a default value:

INSERT INTO customers (id, first_name, last_name, email, country, signup_date)
VALUES (15, 'Olga', 'Novak', 'olga.novak@example.com', 'Czechia', '2024-01-15');

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

Inserting many rows at once

List several groups of values, separated by commas:

INSERT INTO categories (id, name) VALUES (6, 'Garden'), (7, 'Music'), (8, 'Pets');

SELECT * FROM categories WHERE id >= 6;

+----+--------+
| id | name   |
+----+--------+
|  6 | Garden |
|  7 | Music  |
|  8 | Pets   |
+----+--------+

This is a single statement and a single round trip to the server, so it is much faster than three separate inserts. For loading thousands of rows, batches of a few hundred values per statement are a good rule of thumb. If any row in the batch is rejected, the whole statement fails and none of the rows are added.

Auto-generated ids

order_items.id is declared AUTO_INCREMENT, so you do not provide it. MySQL picks the next number:

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (1, 12, 2, 14.99);

SELECT LAST_INSERT_ID() AS new_id;

+--------+
| new_id |
+--------+
|     35 |
+--------+

LAST_INSERT_ID() returns the id your session just created. It is safe even if other users are inserting at the same moment, because each connection has its own value. Applications use it to insert child rows, such as order items right after creating an order.

INSERT ... SELECT

You can fill a table from a query. The column lists must line up:

CREATE TABLE india_customers (id INT PRIMARY KEY, first_name VARCHAR(50), city VARCHAR(50));
INSERT INTO india_customers (id, first_name, city)
SELECT id, first_name, city FROM customers WHERE country = 'India';
SELECT * FROM india_customers;

+----+------------+-----------+
| id | first_name | city      |
+----+------------+-----------+
|  1 | Asha       | Delhi     |
|  6 | Farid      | Mumbai    |
| 11 | Kavya      | Bengaluru |
| 13 | Mira       | Pune      |
+----+------------+-----------+

This is useful for archiving rows, building summary tables, and copying data between tables. The next sections of the course explain CREATE TABLE in detail.

Errors you will see

An insert can be rejected when it breaks a rule. These messages are your friend:

INSERT INTO customers (id, first_name, last_name, email, city, country, signup_date)
VALUES (1, 'Dup', 'Licate', 'dup@example.com', 'Rome', 'Italy', '2024-02-01');

ERROR 1062 (23000): Duplicate entry '1' for key 'customers.PRIMARY'

INSERT INTO orders (id, customer_id, order_date, status) VALUES (21, 999, '2024-08-01', 'pending');

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`shop`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

The first fails because id 1 already exists (the primary key must be unique). The second fails because there is no customer 999, so the foreign key refuses it. Both protect your data from becoming inconsistent.

Common mistakes

• Mismatched counts: three columns and two values gives "Column count doesn't match value count".
• Forgetting quotes around text and dates.
• Inserting without a column list and breaking later when the table changes.
• Doing thousands of single-row inserts in a loop when one multi-row insert would do.
• Assuming ids are contiguous. An AUTO_INCREMENT counter never goes back, so deleted or failed inserts leave gaps.

Exercise

Add a new product called Notebook in category 2 with price 6.50 and stock 500 (use id 16). Then add two more products in a single statement and check how many products exist.

INSERT INTO products (id, name, category_id, price, stock) VALUES (16, 'Notebook', 2, 6.50, 500);
INSERT INTO products (id, name, category_id, price, stock) VALUES
  (17, 'Pen Set', 2, 4.25, 800),
  (18, 'Bookmark', 2, 1.50, 1000);
SELECT COUNT(*) AS products FROM products;

+----------+
| products |
+----------+
|       18 |
+----------+