INSERT statement

The INSERT statement is a DML operator that updates the table by inserting one or more rows into the table. The values inserted into each column of the table can be specified explicitly or obtained from a nested query.

Syntax

INSERT INTO <target_table> [ ( <target_col_name> [ , ... ] ) ]
       {
         VALUES ( { <value> | DEFAULT | NULL } [ , ... ] ) [ , ( ... ) ]  |
         <query>
       }

Parameters

  • <target_table> — the name of the target table into which the rows will be inserted

  • <target_col_name> — the names of the columns into which the values will be inserted. If any table columns are not specified, default values will be inserted into them.

  • VALUES ( value | DEFAULT | NULL [ , …​ ] ) [ , ( …​ ) ] — specifies one or more values to insert into the appropriate columns of the target table.

    • value — an explicitly specified value; can be a literal or an expression.

    • DEFAULT — the default value for the corresponding column of the target table.

    • NULL — an empty value.

    Values are separated by commas.

    You can insert multiple strings by specifying additional sets of values in the expression.

  • query — a query that returns values to insert into the target table. This allows you to insert rows into the target table from one or more source tables.

Example of inserting explicitly specified values

Insert values for the country and capital columns into the capitals table:

CREATE TABLE capitals (country VARCHAR, capital VARCHAR);
INSERT INTO capitals VALUES
    ('Russia', 'Moscow'),
    ('Italy', 'Rome'),
    ('Spain', 'Madrid'),
    ('France', 'Paris');

SELECT * FROM capitals;
+---------+---------+
| country | capital |
+---------+---------+
| France  | Paris   |
+---------+---------+
| Italy   | Rome    |
+---------+---------+
| Russia  | Moscow  |
+---------+---------+
| Spain   | Madrid  |
+---------+---------+

Example of inserting results of a nested query

Now let’s create another table capitals_m and insert the rows from capitals that will be the result of the nested SELECT query. Let’s insert such rows from capitals where the value of capital contains M.

CREATE TABLE capitals_m (country VARCHAR, capital VARCHAR);

INSERT INTO capitals_m (country, capital)
    SELECT * FROM capitals
        WHERE capital LIKE '%M%';

SELECT * FROM capitals_m;
+---------+---------+
| country | capital |
+---------+---------+
| Russia | Moscow |
+---------+---------+
| Spain | Madrid |
+---------+---------+