INSERT one row and many rows
35 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)A new table is empty. INSERT is the SQL statement that adds rows to it: every sign-up on ShopLite, every train ticket booked and every mark entered by a teacher becomes one INSERT in some database.
INSERT INTO ... VALUES
INSERT is the SQL statement that adds rows to a table. INSERT INTO table VALUES (...) adds one row, with the values written in the same order as the table's columns.
Example 1 creates a trains table and adds one row, with the values in the same order as the columns.
Example 1 · Your first INSERT: an IRCTC train
CREATE TABLE trains ( train_no INT PRIMARY KEY, name VARCHAR(50) NOT NULL, from_station CHAR(4) NOT NULL, to_station CHAR(4) NOT NULL, departs TIME NOT NULL, pantry BOOLEAN NULL);
INSERT INTO trains VALUES (12627, 'Karnataka Express', 'SBC', 'NDLS', '19:20:00', TRUE);INSERT INTO trains VALUES (12007, 'Mysuru Shatabdi', 'MAS', 'MYS', '06:00:00', NULL);
SELECT * FROM trains ORDER BY train_no;+----------+-------------------+--------------+------------+----------+--------+ | train_no | name | from_station | to_station | departs | pantry | +----------+-------------------+--------------+------------+----------+--------+ | 12007 | Mysuru Shatabdi | MAS | MYS | 06:00:00 | NULL | | 12627 | Karnataka Express | SBC | NDLS | 19:20:00 | 1 | +----------+-------------------+--------------+------------+----------+--------+
What this code does
- Each
INSERTgives one value per column, in the order ofCREATE TABLE: the number, then the name, the two station codes, the time and the pantry flag. - Text and times are in single quotes;
12627andTRUEare not.NULLmeans "not known yet", so the Shatabdi's pantry showsNULL, andTRUEis stored as1becauseBOOLEANis aTINYINT(1)in MySQL.
Without INSERT, a table is only a shape with no data in it. The values go in brackets, separated by commas, and each must suit its column: numbers are written as they are (12627, 499.00), text and dates go inside single quotes ('Karnataka Express', '2025-06-01'), and a missing value is the keyword NULL, without quotes. To put an apostrophe inside text, write it twice: 'Rubik''s Cube' stores Rubik's Cube. Forgetting the quotes around text is a common slip: MySQL then reads the word as a column name and fails with ERROR 1054 (42S22): Unknown column 'Shatabdi' in 'field list'.
ORDER BY at the end of a SELECT sorts the rows, so you always see them in the same order. The Querying module covers it in full.Naming the columns and why
A column list is the part of an INSERT that names the columns being filled, as in INSERT INTO customers (name, email) VALUES (...). The values then match the names you wrote, not the table's column order.
Naming the columns lets MySQL match each value to its column and fill the rest. Example 2 adds a customer and a product; Example 3 leaves out a required column.
INSERT INTO categories VALUES ('Sports');ERROR 1136 (21S01): Column count doesn't match value count at row 1
Example 2 · ShopLite: a new customer and a new product
INSERT INTO customers (name, email, phone, city, joined_on)VALUES ('Kiran Shah', 'kiran.shah@example.com', NULL, 'Surat', '2025-06-03');
INSERT INTO products (name, category_id, price)VALUES ('Wings of Fire', 5, 350.00);
SELECT customer_id, name, city, joined_on FROM customers WHERE customer_id > 8 ORDER BY customer_id;SELECT product_id, name, price, stock FROM products WHERE product_id > 13 ORDER BY product_id;+-------------+-------------+-----------+------------+ | customer_id | name | city | joined_on | +-------------+-------------+-----------+------------+ | 9 | Sneha Patil | Pune | 2025-04-05 | | 10 | Imran Khan | Hyderabad | 2025-04-18 | | 11 | Kiran Shah | Surat | 2025-06-03 | +-------------+-------------+-----------+------------+ +------------+-------------------+--------+-------+ | product_id | name | price | stock | +------------+-------------------+--------+-------+ | 14 | Rubik's Cube | 299.00 | 70 | | 15 | Gift Card Rs. 500 | 500.00 | 999 | | 16 | Wings of Fire | 350.00 | 0 | +------------+-------------------+--------+-------+
What this code does
- Neither
INSERTnames the id column, soAUTO_INCREMENTgives the next free numbers: customer 11 and product 16. - The product
INSERTleaves outstock, so the column'sDEFAULT 0is used. Naming the columns made it possible to skip both the id and the stock. - The column order in the list is yours to choose; the values only have to follow it.
WHERE customer_id > 8in theSELECTshows just the last few rows.
Example 3 · Mistake: leaving out a required column
INSERT INTO customers (name, email, joined_on)VALUES ('Neha Kapoor', 'neha.kapoor@example.com', '2025-06-04');ERROR 1364 (HY000): Field 'city' doesn't have a default value
What this code does
cityisNOT NULLand has noDEFAULT, so MySQL cannot invent a value. In strict mode it refuses the row with error 1364 and names the missing column.- The fix is to add
cityto the column list and a city toVALUES. Leaving out a column is only safe when it has a default or allowsNULL.
Naming the columns is the form to use almost always. You can leave out the AUTO_INCREMENT id, and MySQL gives the next number. Any other column you leave out gets its DEFAULT value, or NULL if it allows NULL. Your INSERT also keeps working when someone later adds a column with ALTER TABLE. Without a column list, the number of values must equal the number of columns exactly. One value too few gives ERROR 1136 (21S01): Column count doesn't match value count at row 1. Worse, two text values in the wrong order give no error at all: a customer named Pune living in the city Kiran Shah is stored quietly.
Fix the error
Fix the INSERT
Read the real error, then pick the line that fixes it.
Four columns are named, so four values are needed.
Error
ERROR 1136 (21S01): Column count doesn't match value count at row 1
Several rows in one INSERT
A multi-row INSERT lists several bracketed rows after one VALUES, separated by commas. It is one trip to the server, and in strict mode it adds all the rows or none of them.
One statement can add a whole squad. Example 4 inserts several IPL players at once.
INSERT INTO categories (name)VALUES ('Sports'), ('Books'), ('Kitchen');ERROR 1062 (23000): Duplicate entry 'Books' for key 'categories.name'
Example 4 · Many rows at once: an IPL squad
CREATE TABLE ipl_players ( player_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(40) NOT NULL, team CHAR(3) NOT NULL, role ENUM('BAT', 'BOWL', 'ALL', 'WK') NOT NULL, price_cr DECIMAL(5,2) NOT NULL);
INSERT INTO ipl_players (name, team, role, price_cr) VALUES ('Ruturaj Gaikwad', 'CSK', 'BAT', 18.00), ('Ravindra Jadeja', 'CSK', 'ALL', 18.00), ('Jasprit Bumrah', 'MI', 'BOWL', 18.00), ('Sanju Samson', 'RR', 'WK', 18.00), ('Rashid Khan', 'GT', 'BOWL', 18.00);
SELECT * FROM ipl_players ORDER BY player_id;+-----------+-----------------+------+------+----------+ | player_id | name | team | role | price_cr | +-----------+-----------------+------+------+----------+ | 1 | Ruturaj Gaikwad | CSK | BAT | 18.00 | | 2 | Ravindra Jadeja | CSK | ALL | 18.00 | | 3 | Jasprit Bumrah | MI | BOWL | 18.00 | | 4 | Sanju Samson | RR | WK | 18.00 | | 5 | Rashid Khan | GT | BOWL | 18.00 | +-----------+-----------------+------+------+----------+
What this code does
- One
INSERTwith five bracketed rows adds five players in a single trip to the server. The ids 1 to 5 follow the order of the rows. - Every row must have the same four values in the same order as the column list. A row with three values would fail the whole statement with error 1136.
Loading 500 rows this way is much faster than 500 separate INSERTs, and it is the usual way to load sample data, such as the practice database script.
In MySQL's default strict mode with InnoDB tables (the setup this course uses), a row that breaks a rule, such as a duplicate in a UNIQUE column, stops the statement with an error. Fix the bad row and run the whole statement again.
Sports and Kitchen are not in the table either: the duplicate Books cancelled the whole statement.INSERT ... SELECT
INSERT ... SELECT adds every row that a query returns. The SELECT takes the place of VALUES, and it must return the same number of columns as the list after the table name.
Example 5 copies every low-stock product into a restock list with one statement.
Example 5 · INSERT ... SELECT: a restock list
CREATE TABLE restock_list ( product_id INT PRIMARY KEY, name VARCHAR(80) NOT NULL, in_stock INT NOT NULL, to_order INT NOT NULL);
INSERT INTO restock_list (product_id, name, in_stock, to_order)SELECT product_id, name, stock, 50 - stockFROM productsWHERE stock < 10;
SELECT * FROM restock_list ORDER BY product_id;+------------+---------------------+----------+----------+ | product_id | name | in_stock | to_order | +------------+---------------------+----------+----------+ | 3 | iPhone 15 | 8 | 42 | | 5 | HP Victus Gaming | 5 | 45 | | 8 | Logitech M331 Mouse | 0 | 50 | +------------+---------------------+----------+----------+
What this code does
- The
SELECTfinds the three products with fewer than 10 in stock, and each row it returns becomes one row ofrestock_list. Nothing was typed by hand. - The column names differ (
stockgoes intoin_stock) andto_orderis a calculation,50 - stock. Only the number and order of the columns must match.
INSERT ... SELECT saves you from reading rows out and typing them in again. Shops use it to build a restock list, colleges to copy toppers into a merit list, and anyone to keep a copy of rows before a risky change.
WHERE stock < 10in theSELECTkeeps only the matching rows. The Querying module coversWHEREin full.- The query runs first, then every row it returns is inserted, with the same all-or-nothing rule as a multi-row
INSERT.
Next step
Check your understanding
12 quick questions