Adding data, INSERT operator

So far we have only read data. But data in tables does not stand still: new products, customers and orders appear, and each of them needs a new row in a table. That is what the INSERT operator is for.

General query structure with the INSERT operator

INSERT INTO table_name [(table_field [, ...])]
{ VALUES (value [, ...]) [, ...] | select_expression };

Values are either listed after VALUES or returned by a SELECT query.

Field enumeration and value correspondence

You can explicitly specify which fields of the table receive the data. To do this, list the field names in parentheses after the table name:

INSERT INTO table_name (table_field [, ...])
VALUES (value [, ...]);

Important rules:

• The order of values in VALUES must strictly correspond to the order of fields in the enumeration
• The number of values must match the number of specified fields
• If a field is not specified in the enumeration, it will receive the default value (if one is set) or NULL (if the field allows NULL)

For example, if the Goods table has fields good_id, good_name, and type, then the following queries are equivalent:

MySQL 8.4
-- Explicit field specification
INSERT INTO Goods (good_id, good_name, type)
VALUES (20, 'Table', 8);

-- Changed field order - values change accordingly
INSERT INTO Goods (good_name, type, good_id)
VALUES ('Table', 8, 20);

If you don't specify the list of fields, then values must be listed for all table fields in the order they were defined when creating the table:

MySQL 8.4
INSERT INTO Goods
VALUES (20, 'Table', 8);

Differences between INSERT syntaxes

Say we need to add a new item to the Goods list: a dining table. There are two ways to do it.

The familiar way is to list the values after VALUES:

MySQL 8.4
INSERT INTO Goods (good_id, good_name, type)
VALUES (20, 'Table', 8);

But INSERT accepts more than a ready-made list. You can put a SELECT query in place of VALUES, and everything it returns goes into the table. SELECT can even do without FROM. If you just list values in it, it returns them as a single row:

MySQL 8.4
INSERT INTO Goods (good_id, good_name, type)
SELECT 20, 'Table', 8;

Both queries give the same result — a dining table appears in Goods:

When to use which

If the values are known in advance (say, a user has entered them), INSERT ... VALUES is the simplest choice. It can also add several rows at once, each in its own parentheses:

MySQL 8.4
INSERT INTO Goods (good_id, good_name, type)
VALUES
    (20, 'Table', 8),
    (21, 'Chair', 8),
    (22, 'Lamp', 8);

INSERT ... SELECT, on the other hand, comes in handy when new rows have to be built from what's already in the database: to copy records, within a table or into another one, or to save calculation results. For example, let's duplicate all food products and give the copies IDs 100 higher:

MySQL 8.4
INSERT INTO Goods (good_id, good_name, type)
SELECT good_id + 100, good_name, type
FROM Goods
WHERE type = 2;

Run it and look at the end of the table: one query added three rows at once — copies of bread, milk and potato.

Primary key when adding a new record

A primary key value cannot repeat within a table: trying to insert an existing one ends with an error.

So which number is free? The first idea that comes to mind is to take the largest one and add one:

MySQL 8.4
INSERT INTO Goods SELECT MAX(good_id) + 1, 'Table', 8 FROM Goods;

This works, but it is unreliable. If two such queries run at the same time, they can get the same number, and one of them fails with an error. Besides, the approach only suits numeric keys and can hand out a number that used to belong to a deleted record. If old data still refers to that number somewhere, it ends up linked to the new row 💥. That is why in real projects the database itself assigns the number.

Automatic primary key generation

MySQL 8.4
CREATE TABLE Goods (
	good_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	good_name VARCHAR(255),
	type INT
);

MySQL 8.4
INSERT INTO Goods VALUES (NULL, 'Table', 8);

MySQL 8.4
CREATE TABLE Goods (
	good_id SERIAL PRIMARY KEY,
	good_name VARCHAR(255),
	type INT
);

MySQL 8.4
INSERT INTO Goods (good_name, type) VALUES ('Table', 8);

Test yourself

The Goods table already has a product with good_id = 3, and good_id is the primary key. What happens when you run this query?

MySQL 8.4
INSERT INTO Goods (good_id, good_name, type)
VALUES (3, 'chair', 8);