How to Insert Data in MySQL
Last updated August 26, 2026 · By the SaturnSQL team
Use INSERT INTO ... VALUES with one or many row tuples, or INSERT ... SELECT to copy query results. Multi-row inserts are much faster than one INSERT per row.
Listing the column names is worth the extra typing. Without them the tuple has to match the table's column order exactly, so the statement breaks the next time someone adds a column. A single multi-row INSERT is dramatically faster than one statement per row because it is one round trip and one transaction.
INSERT INTO orders (customer_id, amount)
VALUES (101, 42.50), (102, 17.00);Insert from a query
INSERT ... SELECT copies rows entirely on the server, with no data crossing the network to your client. The SELECT column order has to line up with the target's, so name the columns on both sides whenever the tables are not identical. Large copies hold locks for the duration, so batch them with a LIMIT and a loop if that becomes a problem.
INSERT INTO orders_archive
SELECT * FROM orders WHERE created_at < '2025-01-01';Inserting many rows quickly
One INSERT with many VALUES tuples is dramatically faster than a statement per row, because each statement otherwise pays for its own round trip and commit. For a bulk load, batches of a few thousand rows inside an explicit transaction are the practical sweet spot, and LOAD DATA INFILE is faster still when the data is already a file.
START TRANSACTION;
INSERT INTO orders (customer_id, total) VALUES (1, 10), (2, 20), (3, 30);
COMMIT;
LOAD DATA INFILE '/tmp/orders.csv'
INTO TABLE orders
FIELDS TERMINATED BY ',' IGNORE 1 LINES;Common errors
ERROR 1062 (23000): Duplicate entry '1' for key 'customers.PRIMARY'
A unique index rejected the row. The key name after the value tells you which one: PRIMARY for the primary key, otherwise the index name.
Decide what should happen on a clash rather than retrying blind. INSERT IGNORE skips the row, ON DUPLICATE KEY UPDATE turns it into an update. Note that INSERT IGNORE downgrades other errors to warnings too, so it can hide genuine problems.
INSERT INTO customers (id, email) VALUES (1, '[email protected]')
ON DUPLICATE KEY UPDATE email = VALUES(email);ERROR 1364 (HY000): Field 'total' doesn't have a default value
The column is NOT NULL with no default and you left it out of the INSERT. In strict mode, which is the default since 5.7, that is an error rather than a silent zero.
Supply the column, or give it a default if most inserts should not have to.
ALTER TABLE orders ALTER COLUMN total SET DEFAULT 0;ERROR 1406 (22001): Data too long for column 'status' at row 1
The value is longer than the column. The row number tells you which of a multi-row insert failed, and the whole statement is rolled back.
Widen the column or shorten the value. Watch out for multi-byte characters: a VARCHAR(5) holds five characters, but under utf8mb4 an emoji is one character and four bytes, which matters for index length limits rather than this error.
ALTER TABLE orders MODIFY status VARCHAR(50);Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free