INSERT INTO in MySQL: Single Rows, Bulk Inserts, and the Upsert You Actually Want

Q: How do I insert a row only if it does not already exist?

Use INSERT ... ON DUPLICATE KEY UPDATE to update the existing row when a unique or primary key collides, or INSERT IGNORE to skip the conflicting row silently. ON DUPLICATE KEY UPDATE is the precise upsert; INSERT IGNORE is blunter because it also suppresses unrelated errors like data truncation, so choose based on whether you want to update or skip.

The MySQL INSERT INTO statement adds rows to a table, and the basic form - INSERT INTO table (col1, col2) VALUES (val1, val2) - is the first thing everyone learns. The parts that matter in real applications come after that: inserting many rows in one statement instead of looping, handling the row that already exists with ON DUPLICATE KEY UPDATE, and always naming your columns so a schema change does not silently corrupt your data. Those three habits separate a query that works in a tutorial from one that survives production.

One row is easy. The interesting decisions are bulk inserts, conflict handling, and the column-naming discipline that saves you from a subtle data bug later.

The forms, from basic to safe
Bulk insert: one statement, many rows
Handling the row that already exists
Inserting from a query
Getting the new row’s id
How this fits the rest of the stack
FAQ

The forms, from basic to safe

-- Explicit columns - always do this
INSERT INTO users (name, email)
VALUES ('Ada', '[email protected]');

-- Positional (no column list) - fragile, avoid
INSERT INTO users VALUES (NULL, 'Ada', '[email protected]');

Always name the columns. The positional form depends on the exact column order of the table, so the day someone adds a column in the middle, every positional insert quietly writes values into the wrong fields. Naming columns costs a few characters and removes an entire class of silent data corruption.

Bulk insert: one statement, many rows

INSERT INTO users (name, email) VALUES
    ('Ada',  '[email protected]'),
    ('Alan', '[email protected]'),
    ('Grace','[email protected]');

This is not just tidier - it is dramatically faster than three separate INSERT statements. Each statement carries round-trip and transaction overhead; batching rows into one statement pays that cost once. If you are inserting from application code in a loop, that loop is the performance bug. Build one multi-row statement (or use your driver’s batch API) and inserts that took seconds take milliseconds.

Handling the row that already exists

-- Update the existing row instead of erroring on a duplicate key
INSERT INTO users (email, name)
VALUES ('[email protected]', 'Ada Lovelace')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- Skip silently instead of updating
INSERT IGNORE INTO users (email, name)
VALUES ('[email protected]', 'Ada');

ON DUPLICATE KEY UPDATE is MySQL’s upsert: insert if new, update if a unique or primary key collides. INSERT IGNORE instead swallows the error and skips the row. Pick deliberately - IGNORE also hides other errors like data truncation, so it is blunter than it looks. For a true upsert, ON DUPLICATE KEY UPDATE is the precise tool.

Inserting from a query

-- Copy rows that match a condition into another table
INSERT INTO archived_users (id, name, email)
SELECT id, name, email
FROM users
WHERE last_login < '2025-01-01';

INSERT ... SELECT moves or copies data between tables in one server-side operation, without round-tripping the rows through your application. For archiving, backfilling, and denormalizing, this is the pattern - the database does the work where the data already lives, which is always faster than pulling rows out and pushing them back.

Getting the new row’s id

After inserting into a table with an AUTO_INCREMENT primary key, you usually need the generated id. LAST_INSERT_ID() returns it for the current connection:

INSERT INTO users (name, email) VALUES ('Ada', '[email protected]');
SELECT LAST_INSERT_ID();   -- the new id, scoped to your connection

It is connection-scoped, so concurrent inserts from other clients do not affect the value you get back - a common worry that turns out to be a non-issue. Most drivers expose this directly (for example, a lastrowid attribute) so you rarely call it by hand, but knowing it is per-connection is what lets you trust it.

How this fits the rest of the stack

The gap between a per-row insert loop and one bulk statement is the kind of difference that does not show up until the data does - fine on ten rows, a timeout on ten thousand. Sizing a database for real write volume is the same exercise: cheap to get right early, expensive to discover late. The RunxBuild hosting calculator shows a managed database, its connections, and storage as line items, and the RunxBuild dashboard is where you provision one and watch the write load.

Useful related references:

FAQ

What is the basic INSERT INTO syntax in MySQL?

INSERT INTO table_name (column1, column2) VALUES (value1, value2). Always list the columns explicitly rather than relying on positional order, so a later schema change cannot shift values into the wrong fields. To add several rows, separate multiple parenthesized value lists with commas in a single statement, which is far faster than repeating the statement.

How do I insert multiple rows in one MySQL statement?

List several value groups separated by commas: INSERT INTO users (name, email) VALUES (‘Ada’,‘[email protected]’), (‘Alan’,‘[email protected]’). One multi-row statement pays the round-trip and transaction overhead once instead of per row, so it is dramatically faster than looping single inserts from application code. If you are inserting in a loop, batching is the fix.

How do I insert a row only if it does not already exist?

Use INSERT … ON DUPLICATE KEY UPDATE to update the existing row when a unique or primary key collides, or INSERT IGNORE to skip the conflicting row silently. ON DUPLICATE KEY UPDATE is the precise upsert; INSERT IGNORE is blunter because it also suppresses unrelated errors like data truncation, so choose based on whether you want to update or skip.

How do I get the ID of a row I just inserted in MySQL?

Call SELECT LAST_INSERT_ID() after inserting into a table with an AUTO_INCREMENT primary key; it returns the generated id. The value is scoped to your connection, so concurrent inserts from other clients do not change what you get back. Most database drivers expose it directly, such as a lastrowid attribute, so you rarely need to call it manually.

Can I insert data from another table in MySQL?

Yes, with INSERT INTO target (cols) SELECT cols FROM source WHERE … This copies or moves rows entirely on the server without pulling them into your application, which is the efficient way to archive, backfill, or denormalize data. The selected columns must line up with the target column list in order and compatible types.