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 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.
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:
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:
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:
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.
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:
Next, we will insert values into the table. We can execute the below statement to add data into a table:
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:
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:
Second, we will use the INSERT INTO SELECT statement to insert persons located in Texas from the person table into the person_info table:
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: