How to Use MySQL INSERT INTO SELECT query

Posted on: August 20, 2020 by Amilcar Paco

In today’s tutorial, we’ll learn how to use the MySQL INSERT INTO SELECT query to insert data into a table where data comes from the result of SELECT query.

Before we kick off
INSERT Statement
SELECT Statement
INSERT INTO SELECT Statement
Conclusion

Before we kick off

Before we start we need to make sure that we have the MySQL environment properly setup.

INSERT Statement

The INSERT INTO statement is used to insert new records in a table.

Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

But if we want to add values for all the columns of the table, we don’t need to specify the column names in the SQL query. We could simply do as follow:

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

SELECT Statement

The SELECT statement is used to select data from a database.

Syntax

SELECT column1, column2, ...
FROM table_name;

Above we have

column1, column2 are the field names of the table you want to select data from.
table_name is the table’s name.

But if we want to select all the fields in the table, we can use the following syntax:

SELECT * FROM table_name;

Now let’s see how we can use INSERT INTO SELECT statement.

INSERT INTO SELECT Statement

For illustration, consider the following example: Suppose we have two tables here on Codesource database. One table for all the authors and the other one for all the reviewers.

Codesource.author.sql

Codesource.reviewer.sql

Syntax

INSERT INTO table_name_to_insert(column1, column2, ...)
SELECT 
   column1, column2, ... 
FROM 
   table_to_select
WHERE
   condition;

Suppose all reviewers from the USA become the Codesource’s authors. So, we need to copy these reviewers to the author table. First, we’ll SELECT all the reviewers from the USA and then INSERT to author table:

INSERT INTO author
(
    authorName,
    authorSurname,
    authorPhone,
    authorEmail,
    authorAddress1,
    authorCity,
    authorCountry
)
SELECT 
    reviewerName,
    reviewerSurname,
    reviewerPhone,
    reviewerEmail,
    reviewerAddress1,
    reviewerCity,
    reviewerCountry
FROM
    reviewer
WHERE 
    reviewerCountry ='USA';

The result wil be:

Codesource.author.sql