How to Insert Multiple Rows in MySQL (With Examples)

Introduction

Inserting rows one at a time is slow. MySQL lets you insert hundreds or thousands of rows in a single statement, dramatically improving performance.

Insert Multiple Rows with VALUES

The simplest and most common approach — list multiple value tuples in a single INSERT.

-- Insert 5 rows in one statement:
INSERT INTO products (name, price, category)
VALUES
  ('Laptop', 999.99, 'Electronics'),
  ('Keyboard', 49.99, 'Electronics'),
  ('Desk', 299.99, 'Furniture'),
  ('Monitor', 399.99, 'Electronics'),
  ('Chair', 199.99, 'Furniture');

INSERT INTO ... SELECT (Copy from Another Table)

Copy rows from one table to another using INSERT SELECT. Great for data migration and ETL.

-- Copy active users to a new table:
INSERT INTO active_users (id, name, email)
SELECT id, name, email
FROM users
WHERE active = 1;

-- Copy with transformation:
INSERT INTO order_summary (customer_id, total_orders, total_spent)
SELECT
  customer_id,
  COUNT(*) AS total_orders,
  SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id;

Handle Duplicates with ON DUPLICATE KEY UPDATE

When inserting multiple rows, handle duplicates gracefully with upsert syntax.

-- Insert or update on duplicate:
INSERT INTO products (sku, name, price)
VALUES
  ('SKU001', 'Laptop', 999.99),
  ('SKU002', 'Keyboard', 49.99),
  ('SKU003', 'Monitor', 399.99)
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  price = VALUES(price);

Bulk Load with LOAD DATA INFILE

For truly massive inserts (millions of rows), LOAD DATA INFILE is 10-20x faster than INSERT statements.

-- Load from CSV file:
LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(name, price, category);

Performance Tips for Bulk Inserts

Maximize insert speed with these proven techniques.

-- 1. Disable indexes during bulk insert:
ALTER TABLE products DISABLE KEYS;
INSERT INTO products ... (thousands of rows);
ALTER TABLE products ENABLE KEYS;

-- 2. Wrap in a single transaction:
START TRANSACTION;
INSERT INTO logs VALUES (1, 'event1', NOW());
INSERT INTO logs VALUES (2, 'event2', NOW());
-- ... hundreds more
COMMIT;

-- 3. Increase bulk insert buffer:
SET SESSION bulk_insert_buffer_size = 256 * 1024 * 1024;

Best Practices

• Always use multi-row INSERT instead of individual INSERT statements
• For 100K+ rows, use LOAD DATA INFILE
• Wrap bulk inserts in a transaction for atomicity and speed
• Disable keys and re-enable after bulk inserts on large tables
• Use INSERT IGNORE or ON DUPLICATE KEY UPDATE for data that may contain duplicates

Generate MySQL Queries with AI2SQL

Skip the syntax lookup. Describe what you need in plain English and AI2SQL generates the correct MySQL query instantly.

Frequently Asked Questions