MySQL Insert into Select in One Command

We are all familiar with the basic MySQL INSERT INTO clause that allows us to insert values into a table.

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.

Basic Usage

If you can recall the basic INSERT INTO clause in MySQL, we can add data as:

INSERT INTO tbl_name VALUES (values1…valuesN);

However, we can use the SELECT statement instead of the VALUES clause.

The general syntax is:

INSERT INTO tbl_name(cols) SELECT cols_list FROM tbl_name WHERE condition;

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:

https://dev.mysql.com/doc/index-other.html

Let us start by creating a table that will contain the data as shown in the queries below:

USE sakila;
DROP TABLE IF EXISTS currated_info;
CREATE TABLE currated_info(
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(100),
rating VARCHAR(50),
film_length INT
);

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> USE sakila;
Database changed
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.

Conclusion

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.

MySQL Insert into Select in One Command

Basic Usage

Example Use Case

Conclusion

About the author

John Otieno