INSERT

Adding rows with INSERT: named columns, multi-row VALUES lists, the SET form, and INSERT ... SELECT.

SyntaxINSERT [IGNORE] INTO tbl_name [(col_name, ...)] {VALUES (value_list), ... | SET assignment_list | SELECT ...}

INSERT is how rows get into a table. In MySQL 8.4 it has three shapes that differ in readability, not in what they do to the table.

The three forms

The VALUES form writes one or more literal rows. The SET form assigns column by column but writes exactly one row. The SELECT form feeds rows straight from a query, which is how you copy, archive or backfill without pulling data through the client and pushing it back.

Shorthand and longhand

  • INSERT INTO orders VALUES (1, 'pending') is shorthand for INSERT INTO orders (id, status) VALUES (1, 'pending'). Omitting the column list means every column, in the table's declared order.
  • VALUE is an accepted synonym for VALUES. Identical meaning.
  • VALUES (1,2,3), (4,5,6) and VALUES ROW(1,2,3), ROW(4,5,6) insert the same rows. The ROW() spelling is the row-constructor list, available since MySQL 8.0.19.
  • INSERT INTO t SET a = 1, b = 2 is the same statement as INSERT INTO t (a, b) VALUES (1, 2).

What trips people up

A multi-row INSERT is one statement: if row 400 violates a constraint, none of the 1,000 rows land. INSERT IGNORE demotes those errors to warnings, but it also silences truncation and conversion errors, so it hides more than duplicates. A later column can reference an earlier one in the same row list (VALUES (15, col1 * 2)), but an AUTO_INCREMENT column read this way yields 0, because its value is generated after the other assignments.

Example

Example · sql
-- Naming the columns is what makes this statement survive a later ALTER TABLE.
INSERT INTO orders (customer_id, status, total_cents, placed_at)
VALUES (1042, 'pending', 4999, NOW());

-- Capture the generated id before running anything else on this connection:
-- LAST_INSERT_ID() is reset by the next INSERT that touches an AUTO_INCREMENT column.
SET @order_id = LAST_INSERT_ID();

-- One multi-row statement instead of two round trips. All or nothing:
-- if either row fails a constraint, neither is written.
INSERT INTO order_items (order_id, sku, qty, unit_cents)
VALUES
  (@order_id, 'KB-87',  1, 3499),
  (@order_id, 'CBL-2C', 2,  750);

When to use it

  • Write a new record and immediately capture its AUTO_INCREMENT id for child rows.
  • Load a few hundred rows in one statement instead of one INSERT per row.
  • Copy qualifying rows into an archive or reporting table entirely server-side.
  • Backfill a newly added column's table from an existing one during a migration.

More examples

The SET form and its VALUES longhand

These two statements are the same insert. The SET form keeps each column next to its value, which is easier to read and diff when a table has twenty columns; the trade-off is that it accepts only a single row, so any batch has to use VALUES. Being able to read both matters because ORMs and migration tools emit whichever form their author preferred.

Example · sql
-- Shorthand: the SET form. Reads like an UPDATE, writes exactly one row.
INSERT INTO audit_log
SET actor_id   = 7,
    action     = 'order.cancelled',
    target_id  = 1042,
    created_at = NOW();

-- Longhand: the identical statement written with VALUES.
INSERT INTO audit_log (actor_id, action, target_id, created_at)
VALUES (7, 'order.cancelled', 1042, NOW());

INSERT ... SELECT for archiving

Reach for INSERT ... SELECT whenever the data already lives in the database: archiving, denormalising into a reporting table, seeding a new table during a migration. Because the rows never leave the server it is dramatically faster than SELECT-then-INSERT from application code. Wrap the INSERT and the DELETE in one transaction so a crash between them cannot lose rows.

Example · sql
-- Move finished orders older than a year into a cold table, server-side.
-- The column list on the INSERT and the SELECT list are matched by position, not by name.
INSERT INTO orders_archive (id, customer_id, status, total_cents, placed_at)
SELECT o.id, o.customer_id, o.status, o.total_cents, o.placed_at
FROM   orders AS o
WHERE  o.status IN ('shipped', 'cancelled')
  AND  o.placed_at < NOW() - INTERVAL 1 YEAR;

-- Only now delete the originals, using the same predicate.
DELETE FROM orders
WHERE  status IN ('shipped', 'cancelled')
  AND  placed_at < NOW() - INTERVAL 1 YEAR;

DEFAULT and the ROW() constructor

DEFAULT is the escape hatch for positional inserts: you keep the column in the list so the statement still documents the shape of the row, but let the schema supply the value. The ROW() form is worth recognising because MySQL's own manual uses it in examples, and it is the same syntax the table value constructor uses elsewhere in the language.

Example · sql
-- DEFAULT tells MySQL to apply the column's declared default explicitly,
-- which is how you skip a column in the middle of a positional list.
INSERT INTO feature_flags (name, enabled, rollout_pct, created_at)
VALUES ('new_checkout', DEFAULT, 5, DEFAULT);

-- The row-constructor spelling (MySQL 8.0.19 and later) inserts identically
-- to a plain comma-separated VALUES list. Useful when you want each row visually bracketed.
INSERT INTO feature_flags (name, enabled, rollout_pct)
VALUES ROW('dark_mode', 1, 100),
       ROW('beta_search', 0, 0);

Related

sql upsertsql returningsql bulk loadssql create tablesql not null

Discussion

  • Be the first to comment on this lesson.