INSERT

INSERT

INSERT, Multi-Row Inserts, and INSERT ... SELECT

INSERT adds rows. Columns you leave out take their default: NULL, a DEFAULT clause, or the next AUTO_INCREMENT number.

Four ways to insert, and two mistakesSQL

INSERT INTO authors (name, country, born) VALUES ('Rasmus Lerdorf', 'CA', 1968);
INSERT INTO authors (name, country, born) VALUES
  ('Michael Widenius', 'FI', 1962), ('David Axmark', 'SE', 1962),
  ('Taylor Otwell', 'US', NULL);
INSERT INTO authors SET name = 'Linus Torvalds', country = 'FI';
INSERT INTO authors VALUES ('Tim Berners-Lee', 'GB', 1955);   -- no column list
INSERT INTO customers (email, name, country) VALUES ('ana@example.com', 'Ana S.', 'BR');
CREATE TABLE orders_archive LIKE orders;
INSERT INTO orders_archive SELECT * FROM orders WHERE status = 'cancelled';

Output

Query OK, 1 row affected (0.007 sec)
Query OK, 3 rows affected (0.005 sec)
Records: 3  Duplicates: 0  Warnings: 0
Query OK, 1 row affected (0.005 sec)
ERROR 1136 (21S01): Column count doesn't match value count at row 1
ERROR 1062 (23000): Duplicate entry 'ana@example.com' for key 'customers.email'
Query OK, 0 rows affected (0.053 sec)
Query OK, 1 row affected (0.014 sec)
Records: 1  Duplicates: 0  Warnings: 0

A multi-row insert is parsed and committed once, so use it for bulk loads. Afterward, LAST_INSERT_ID() returns 2, the first new id. It is per connection, which is why PHP can safely read back a new order's id (PHP).

Omitting the column list (error 1136) demands every column in table order, and when the counts happen to match, values land silently in the wrong columns. Error 1062 is the UNIQUE index on email at work. INSERT IGNORE would skip that row, but it downgrades every error in the statement to a warning. The last statement copies the cancelled order into a clone made with CREATE TABLE ... LIKE; the SELECT may filter, join and compute.

MENU