MySQL INSERT INTO SELECT Statement – Easy Guide

In this tutorial, we will study an easy yet powerful statement- INSERT INTO SELECT. In official MySQL documentation, it is described as INSERT … SELECT; however, people prefer calling it INSERT INTO SELECT. We will also be looking at its syntax, examples, and points that you should avoid getting successful results from the given statement.

Also read: MySQL Select Statement – Fetching Data from MySQL Databases

What is INSERT INTO SELECT Statement

In MySQL, you can use a SELECT statement to get the data from the table and the INSERT statement to insert the data into the table. The INSERT INTO SELECT statement is, you can say, a combination of these above two statements.

When you want to copy all the data or data of only particular columns from one table to another, you can use the INSERT INTO SELECT statement. Moreover, you can insert the data from more than one table into the new table using this statement.

However, your query has restrictions before using the INSERT INTO SELECT statement. We will see the syntax of the INSERT INTO SELECT statement first and then focus on the conditions of using it, followed by some examples.

Syntax of INSERT INTO SELECT Statement

The INSERT INTO SELECT statement is simply a combination of the INSERT and SELECT statements, so its syntax is straightforward.

However, the outcomes can have multiple possibilities based on different syntax.

Below is a syntax for copying all table rows of the first table into the second table.

INSERT INTO table1
SELECT * FROM table2
WHERE conditionCode language: SQL (Structured Query Language) (sql)

From MySQL version 8.0.19, the above query can be minimized using a TABLE statement.

INSERT INTO table1 TABLE table2Code language: SQL (Structured Query Language) (sql)

The TABLE statement does the same work as the SELECT * FROM statement above. This is useful when you want to insert all columns from the first table to the second table, and no conditional statements are required.

What if you want to insert only a few columns from the first table to the second table? You can use the following syntax in that case-

INSERT INTO table1(column list)
SELECT (column list) FROM table2
WHERE conditionCode language: SQL (Structured Query Language) (sql)

Before using this syntax, note –

• The data type of source table columns and target table columns must match.
• If no value is present in the source table column, the target column will be filled with NULL values.
• All the values will be replicated from source table to the target table without changing the previous data of target table.
• You can use same table name as a source as well as target. However, this is not possible in subquery.
• You can not use conditional statements when using a TABLE statment.

That’s enough theory for now. Let’s see some examples.

Examples on INSERT INTO SELECT Statement

We will first create two tables and use them throughout the tutorial. The data in these two tables won’t be manipulated till the end, but the newly created table will be truncated and refilled with data whenever required. Here we go!

First, create table s1 with fields id as a primary key and name and insert values in it.

CREATE TABLE s1(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);Code language: SQL (Structured Query Language) (sql)

INSERT INTO s1 VALUES
("tejas"),("yogesh"),("dipak"),("vishal"),("rushi"),("ganesh");Code language: SQL (Structured Query Language) (sql)

Let’s check if all the data is inserted successfully-

SELECT * FROM s1;Code language: SQL (Structured Query Language) (sql)

Now create a second table s2 with fields id as a primary key and the age.

CREATE TABLE s2(
id INT PRIMARY KEY AUTO_INCREMENT,
age INT
);Code language: SQL (Structured Query Language) (sql)

INSERT INTO s2 VALUES
(20),(15),(30),(10),(60),(12);Code language: SQL (Structured Query Language) (sql)

Let’s check the table data-

SELECT * FROM s2;Code language: SQL (Structured Query Language) (sql)

At last, we have to create an empty table s3 with fields id, name, and age. This table will be used for some slightly tricky queries.

CREATE TABLE s3(
id INT,
name VARCHAR(20),
age INT
);Code language: SQL (Structured Query Language) (sql)

We are all set now. Let’s take examples of the INSERT INTO SELECT statement from easy to complex ones.

Copy All Rows From One Table to Another

Copying all rows from one table to another is the most straightforward task with the INSERT INTO SELECT statement. We can achieve this in two ways, using either SELECT * FROM statement or TABLE statement. Note that the TABLE statement is available in MySQL version 8.0.19 and above.

First, we will create a table s1Copy quickly.

CREATE TABLE s1Copy(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);Code language: SQL (Structured Query Language) (sql)

Note that the target columns should be the same type as the source columns.

Now, let’s copy the data-

Method 1-

INSERT INTO s1Copy SELECT * FROM s1;Code language: SQL (Structured Query Language) (sql)

The query was executed successfully; let’s check if the values are copied correctly in table s1Copy-

SELECT * FROM s1Copy;Code language: SQL (Structured Query Language) (sql)

As you can see, all the values are copied precisely the same as table s1.

Now, we will truncate the table data and use the second method on the same table to copy the data from table s1.

TRUNCATE s1Copy;Code language: SQL (Structured Query Language) (sql)

Method 2-

INSERT INTO s1Copy TABLE s1;Code language: SQL (Structured Query Language) (sql)

Let’s check the s1Copy table data-

SELECT * FROM s1Copy;Code language: SQL (Structured Query Language) (sql)

We got the same copy of table s1 in the table s1Copy.

Copy Particular Column Data From One Table Into Another

Copying only particular attribute rows from one table to another is uncomplicated too. You only have to specify the column names for both tables in the query statement. Note that the source and target column data types must match.

If the target table has more columns than the source table that are not specified in the query statement, it will be filled with NULL values.

First, create a table of the name s2Copy.

CREATE TABLE s2Copy(id INT PRIMARY KEY AUTO_INCREMENT, age INT);Code language: SQL (Structured Query Language) (sql)

The table is successfully created. We will now copy the age values from table s2 into the id column of s2Copy. Note that we are not inserting any value in the age column of table s2Copy. Let’s see the result after query execution.

INSERT INTO s2Copy(id) SELECT(age) FROM s2;Code language: SQL (Structured Query Language) (sql)

The data type of both the id and age column from both tables are the same so that the query will execute successfully without any error.

Let’s check how and what data is copied into the s2Copy table.

 SELECT* FROM s2Copy;Code language: SQL (Structured Query Language) (sql)

As you can see, all the age row values from the s2 table are copied into the id column of the s2Copy table, but the age column of s2Copy is filled with NULL values. That is because we haven’t specified which data to be copied/stored into the age column of the s2Copy table.

Using WHERE Clause to Copy Particular Rows

You can use the WHERE clause to copy only specific row data from one table to another. For example, if you’re going to copy students’ names starting with the letter ‘A’ to another table, you can use the WHERE clause in the INSERT INTO SELECT statement. Check the practical example below-

Example- Copy all the names that consist of only five letters from table s1 to s1Copy.

Note- Before writing a query, truncate all the previous data from the table s1Copy.

INSERT INTO s1Copy(name) SELECT(name) FROM s1
WHERE CHAR_LENGTH(name) = 5;Code language: SQL (Structured Query Language) (sql)

We have used the MySQL function CHAR_LENGTH() to find the length of the name and checked if the length is five, then copied it into the target table.

Let’s check the target table s1Copy.

SELECT * FROM s1Copy;Code language: SQL (Structured Query Language) (sql)

We have got the expected result here.

Copy Rows From Two or More Tables

The INSERT INTO SELECT statement also supports copying rows from two or more tables with straightforward syntax. For example, copying the values from table s1 and s2 into table s3.

Let’s check the practical example below-

Statement- Copy the names from table s1 and age from s2 on the key attribute ‘id’ into table s3.

Here, we will use the conditional statement WHERE clause to compare the ids from both tables.

INSERT INTO s3(id,name,age) SELECT s1.id,s1.name,s2.age
FROM s1,s2 WHERE s1.id=s2.id;Code language: SQL (Structured Query Language) (sql)

The query was executed successfully. Let’s see the s3 table data-

SELECT * FROM s3;Code language: SQL (Structured Query Language) (sql)

As you can see, we have successfully copied the data from table s1 and s2 into table s3 without any error.

Conclusion

In this tutorial, we have learned about the INSERT INTO SELECT statement and went through some examples. The INSERT INTO SELECT statement can be used in many different ways. You can even use it to copy data from another database, copy the data from the same table, and so on. You can play around with this statement and practice it to use in your application. See you in the following tutorial!

References

Official MySQL documentation for INSERT INTO SELECT statement.

Stackoverflow thread for related questions and answers.