How to INSERT Data in MySQL

The INSERT statement adds new rows to a MySQL table. Whether you are seeding a development database, importing data from a file, or saving user input from an application, INSERT is the command you will reach for. MySQL offers several variations to handle different scenarios efficiently.

Basic INSERT Syntax

Specify the target table, the columns to populate, and the values for each column. The order of values must match the order of the listed columns.

INSERT INTO employees (first_name, last_name, email, salary)
VALUES ('Ana', 'Garcia', 'ana.garcia@example.com', 72000);

Inserting Multiple Rows

Instead of running separate INSERT statements, you can add several rows in a single command. This is significantly faster because MySQL processes one statement instead of many and reduces network round trips.

INSERT INTO products (name, category, price)
VALUES
  ('Wireless Mouse', 'Electronics', 29.99),
  ('USB-C Cable', 'Electronics', 12.50),
  ('Notebook', 'Office', 4.99);

INSERT with SELECT

You can populate a table using the results of a query. This is useful for copying data between tables, archiving records, or creating summary tables.

INSERT INTO archived_orders (order_id, customer_id, total, order_date)
SELECT order_id, customer_id, total, order_date
FROM orders
WHERE order_date < '2024-01-01';

Handling Duplicates

When a row violates a unique key constraint, MySQL raises an error by default. You can handle this gracefully with INSERT IGNORE (silently skip duplicates) or ON DUPLICATE KEY UPDATE (update the existing row instead).

-- Skip if email already exists
INSERT IGNORE INTO employees (email, first_name, last_name)
VALUES ('ana.garcia@example.com', 'Ana', 'Garcia');

-- Update salary if employee email already exists
INSERT INTO employees (email, first_name, salary)
VALUES ('ana.garcia@example.com', 'Ana', 78000)
ON DUPLICATE KEY UPDATE salary = VALUES(salary);

Getting the Last Inserted ID

When a table has an AUTO_INCREMENT primary key, MySQL assigns the next value automatically. After inserting, useLAST_INSERT_ID() to retrieve the generated ID. This is essential for inserting related rows into child tables.

INSERT INTO orders (customer_id, total)
VALUES (42, 199.99);

SELECT LAST_INSERT_ID() AS new_order_id;

Best Practices

Always list column names explicitly rather than relying on column order. Use multi-row inserts for bulk data. Wrap related inserts in a transaction to keep your data consistent if one statement fails.