INSERT

So far you have only read data. Real applications also write it: a new customer signs up, a new product arrives, an order is placed. Every one of these actions ends in an INSERT statement that adds a row to a table.

In this lesson you will add single rows and many rows at once, leave columns empty or let them take default values, copy rows from one table into another, and learn how MySQL creates ID numbers for you.

Adding one row

An INSERT statement names the table, lists the columns you are filling, and then gives the values in the same order:

INSERT INTO customers (customer_id, first_name, last_name, city, state, email, joined_on)
VALUES (13, 'Kavya', 'Menon', 'Kochi', 'Kerala', 'kavya.menon@example.com', '2025-06-02');

SELECT customer_id, first_name, city, email, joined_on
FROM customers
WHERE customer_id >= 12;

customer_id | first_name | city  | email                   | joined_on
------------+------------+-------+-------------------------+-----------
12          | Divya      | Pune  | divya.joshi@example.com | 2025-01-09
13          | Kavya      | Kochi | kavya.menon@example.com | 2025-06-02
(2 rows)

The first value goes into the first column, the second value into the second column, and so on. Text and dates go inside single quotes. Numbers do not need quotes. Dates use the format 'YYYY-MM-DD', so 2 June 2025 is '2025-06-02'.

The browser editor resets the practice database every time you click Run, so you can experiment freely. In MySQL, the row stays until you delete it.

Always list the columns

SQL lets you skip the column list and give a value for every column in table order:

INSERT INTO products VALUES (16, 'Phone Tripod', 'Accessories', 699.00, 40);

This works today, but it is fragile. If someone later adds a column to products, the statement fails with ERROR 1136 (21S01): Column count doesn't match value count at row 1. Worse, if two columns swap places, values can land in the wrong column without any error. Writing the column list makes your intention clear and keeps working when the table changes.

Adding many rows at once

You can insert several rows in one statement by separating the value groups with commas. This is shorter to write and much faster than many single inserts, because MySQL handles the whole batch together.

INSERT INTO products (product_id, product_name, category, price, stock)
VALUES
  (13, 'Webcam Cover (Pack of 3)', 'Accessories', 199.00, 300),
  (14, 'Smart Watch', 'Wearables', 3999.00, 20),
  (15, 'Fitness Band', 'Wearables', 1799.00, 35);

SELECT product_id, product_name, category, price
FROM products
WHERE product_id > 12;

product_id | product_name             | category    | price
-----------+--------------------------+-------------+------
13         | Webcam Cover (Pack of 3) | Accessories | 199
14         | Smart Watch              | Wearables   | 3999
15         | Fitness Band             | Wearables   | 1799
(3 rows)

MySQL would show the prices as 199.00, 3999.00 and 1799.00, because the column is DECIMAL(10,2). The browser stores them as whole numbers, so it prints 199.

Missing values, NULL and quotes inside text

If you leave a column out of the list, it gets its default value. For a column without a declared default that allows NULL, that means NULL. In the next example the new customer has no email address yet, so the email column is simply not mentioned. The surname also contains an apostrophe, which you write as two single quotes inside the text:

INSERT INTO customers (customer_id, first_name, last_name, city, state, joined_on)
VALUES (14, 'Neil', 'D''Souza', 'Panaji', 'Goa', '2025-06-05');

SELECT first_name, last_name, city, email
FROM customers
WHERE customer_id = 14;

first_name | last_name | city   | email
-----------+-----------+--------+------
Neil       | D'Souza   | Panaji | NULL
(1 row)

You can also write NULL in the values list to make the gap obvious. If a column is declared NOT NULL and has no default, you must give it a value: leaving out city here would make MySQL stop with an error such as Field 'city' doesn't have a default value. You will learn about defaults and NOT NULL in Constraints & Keys.

Copying rows with INSERT ... SELECT

Instead of VALUES, you can feed an INSERT with the result of a SELECT. The shop's events team wants a separate contact list of Haryana customers with the full name in one column:

CREATE TABLE haryana_contacts (
  customer_id INTEGER PRIMARY KEY,
  full_name VARCHAR(90) NOT NULL,
  city VARCHAR(40) NOT NULL
);

INSERT INTO haryana_contacts (customer_id, full_name, city)
SELECT customer_id, CONCAT(first_name, ' ', last_name), city
FROM customers
WHERE state = 'Haryana';

SELECT * FROM haryana_contacts;

customer_id | full_name    | city
------------+--------------+---------
1           | Aarav Sharma | Rohtak
7           | Vikram Yadav | Rohtak
10          | Isha Kapoor  | Gurugram
(3 rows)

There is no VALUES keyword: the rows come straight from the query. The columns of the SELECT must match the column list of the INSERT in number and order. This pattern is common for archiving old orders, filling report tables and moving data during a redesign.

Automatic IDs with AUTO_INCREMENT

In the examples above you typed every ID by hand, because the practice database must also run in the browser. In real MySQL tables you normally let the database choose the next number with AUTO_INCREMENT:

CREATE TABLE enquiries (
  enquiry_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  phone VARCHAR(15) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO enquiries (name, phone) VALUES ('Ananya Iyer', '+91 98400 12345');
INSERT INTO enquiries (name, phone) VALUES ('Farhan Khan', '+91 94150 67890');

SELECT LAST_INSERT_ID();

The first row gets enquiry_id 1 and the second gets 2. created_at fills itself with the current date and time. LAST_INSERT_ID() returns the ID generated by your connection's last insert, which is how an application finds the number of the order it has just created. You will create tables like this in CREATE DATABASE & TABLE.

When the key already exists

A primary key must be unique. Inserting a second row with customer_id 1 fails with ERROR 1062 (23000): Duplicate entry '1' for key 'customers.PRIMARY'. Sometimes you want "insert, or update if it already exists" instead. MySQL has a special clause for this:

INSERT INTO products (product_id, product_name, category, price, stock)
VALUES (10, '64GB Pen Drive', 'Storage', 549.00, 50) AS new
ON DUPLICATE KEY UPDATE stock = stock + new.stock;

Product 10 already exists, so instead of failing, MySQL adds 50 to its stock. AS new gives the incoming row a name (supported since MySQL 8.0.19). You may see VALUES(stock) in older code for the same purpose; that form is deprecated. INSERT IGNORE also exists, but it silently skips rows with problems, which can hide real mistakes.

Common mistakes

• No column list. Always name the columns; it protects you when the table changes.
• Quoting numbers or forgetting quotes on text. Write 'Kochi' with quotes and 199.00 without.
• Wrong date format. '05-06-2025' is not a valid MySQL date. Use '2025-06-05'.
• Building INSERT statements by joining user input into a string. This allows SQL injection. Applications must use prepared statements, as shown in CRUD with Prepared Statements.

Key points

• INSERT INTO table (columns) VALUES (values) adds a row; values match the columns by position.
• Always write the column list.
• Several value groups separated by commas insert many rows in one statement.
• Columns you leave out get their default value or NULL; write '' for an apostrophe inside text.
• INSERT ... SELECT copies the result of a query into a table.
• AUTO_INCREMENT creates IDs for you, and LAST_INSERT_ID() tells you the last one.

INSERT INTO products (product_id, product_name, category, price, stock)
VALUES
  (16, 'Learn SQL in Hindi (Book)', 'Books', 399.00, 60),
  (17, 'JavaScript Projects (Book)', 'Books', 549.00, 40);

SELECT product_name, price, stock
FROM products
WHERE category = 'Books'
ORDER BY price;