In this tutorial, we will defer from that and look at the INSERT INTO — SELECT statement that we can use to insert values in a table where the values are from the result of a SELECT statement.
If you can recall the basic INSERT INTO clause in MySQL, we can add data as:
However, we can use the SELECT statement instead of the VALUES clause.
The general syntax is:
The query above uses the SELECT clause to select specified values from other tables and insert them into the set table.
In most cases, we use the INSERT INTO — SELECT clause when copying values from another table or only sections of the values from a table.
Example Use Case
Let me illustrate how we can use the INSERT INTO with the SELECT statement using a real-world database.
NOTE: For this example, I will be using the Sakila database for illustrations. Feel free to use any other database or download a copy of the Sakila sample database from the resource provided below:
Let us start by creating a table that will contain the data as shown in the queries below:
Finally, we can use INSERT INTO statement for the table above as shown in the query below:
INSERT INTO currated_info (title, rating, film_length) SELECT title, rating, length FROM film;
Once the query has executed successfully, we can query the curated_table and see the data as shown below:
mysql> select * FROM currated_info LIMIT 5;
| id | title | rating | film_length |
| 1 | ACADEMY DINOSAUR | PG | 86 |
| 2 | ACE GOLDFINGER | G | 48 |
| 3 | ADAPTATION HOLES | NC-17 | 50 |
| 4 | AFFAIR PREJUDICE | G | 117 |
| 5 | AFRICAN EGG | G | 130 |
5 rows in set (0.00 sec)
As you can see, we can use the select statement instead of the values clause to add values to a table.
NOTE: Be careful when using the select statement on a large database as it might slow down the database or cause a crash.
In this tutorial, we quickly learned how to use the MySQL INSERT INTO clause with the SELECT statement to add data from the result of a select query.