MySQL INSERT INTO SELECT

Sometimes we want to insert data of one table into the other table in the same or different database. It is not very easy to enter these data using the INSERT query manually. We can optimize this process with the use of MySQL INSERT INTO SELECT query. It allows us to populate the MySQL tables quickly. This section will cover the INSERT INTO SELECT command, syntax, and its use cases.

The INSERT INTO SELECT statement in MySQL allows us to insert values into a table where data comes from a SELECT query. In other words, this query copies data from one table and inserts them in the other table. We must consider the following point before using this statement:

The data types in source and target tables must be the same.
The existing records in the target table should be unaffected.

The INSERT INTO SELECT command is advantageous when we need to copy data from one table to another table or to summarize data from more than one table into a single table.

Syntax

Earlier, we have used the INSERT command for adding single or multiple records into a table along with listing column values in the VALUES clause as follows:

INSERT INTO table_name (column_list)
VALUES (value_list);

The following is the basic syntax that illustrates the use of the INSERT INTO SELECT command in MySQL. If we want to copy all data from one table into another table, we can use the below statement:

INSERT INTO table_name2
SELECT * FROM table_name1
WHERE condition;

From the MySQL version 8.0.19, we can use a TABLE statement in place of SELECT query to get the same output, as shown below:

Here, TABLE table1 is equivalent to SELECT * FROM table1. When we want to add all records from the source table into the target table without filtering the values, it is used.

If we want to copy only some columns from one table to another table, we can use the below statement:

INSERT INTO table_name2 (column_list)
SELECT column_list 
FROM table_name1
WHERE condition;

In this syntax, we have used a SELECT statement instead of using the VALUES clause. Here SELECT command retrieves values from one or more tables.

Parameter Explanation

The INSERT INTO SELECT statement uses the following parameters:

table_name1: It is the name of a source table.

table_name2: It is the name of a target table where we will copy source table data.

column_list: It represents the column names of the table.

condition: it is used to filter the table data.

MySQL INSERT INTO SELECT Example

Let us understand how the INSERT INTO SELECT statement works in MySQL with the help of an example. First, we need to create a table named "person" using the statement given below:

CREATE TABLE person (  
  id int AUTO_INCREMENT PRIMARY KEY,  
  name varchar(45) NOT NULL,  
  email varchar(45) NOT NULL,  
  city varchar(25) NOT NULL  
);

Next, we will insert values into the table. We can execute the below statement to add data into a table:

INSERT INTO person (id, name, email, city)   
VALUES (1,'Stephen', 'stephen@javatpoint.com', 'Texas'),   
(2, 'Joseph', 'Joseph@javatpoint.com', 'Alaska'),   
(3, 'Peter', 'Peter@javatpoint.com', 'Texas'),
(4,'Donald', 'donald@javatpoint.com', 'New York'),   
(5, 'Kevin', 'kevin@javatpoint.com', 'Texas');  

We can verify the data by executing the SELECT statement:

After executing the query, we can see the below output where we have five rows into the table:

Suppose we want to insert a person's name who belongs to Texas City into another table. The following query is used to search all person who locates in Texas:

SELECT name, email, city
FROM person
WHERE city = 'Texas';

Now, we will create another table named person_info that have a same number of column, and data types in the same order as of the above table:

CREATE TABLE person_info (  
  person_id int AUTO_INCREMENT PRIMARY KEY,  
  person_name varchar(45) NOT NULL,  
  email varchar(45) NOT NULL,  
  city varchar(25) NOT NULL  
);

Second, we will use the INSERT INTO SELECT statement to insert persons located in Texas from the person table into the person_info table:

INSERT INTO person_info (person_name, email, city)
SELECT name, email, city
FROM person
WHERE city = 'Texas';

After executing this statement, we can verify the insert operation using the SELECT query. We will get the below output where all persons located in the Texas City inserted successfully.

Suppose we want to insert all person's table data into the person_info table without filtering any values; we can do this using the below statement:

Execute the SELECT statement to verify the data. Here is the output: