In this article, we will explain SQL INSERT INTO SELECT Statement through examples.
You might have used a ‘select’ statement, and the ‘insert into’ statement in SQL separately. The select statement is used to fetch data from the database whereas the ‘insert into’ statement inserts data into the table. In this particular example, we will learn the usage of the insert into select statement in SQL.
1. About SQL INSERT INTO SELECT Statement
The SQL Insert Into Select statement is used to copy the data from one table ( let us call it the source table ) into another table ( let us call it the destination table).
Note:
- This statement only works in the case if the data types of both the source and destination tables match.
- The already existing data in the destination table will not have any changes once the insert into a select statement is applied to the destination table.
2. INSERT INTO SELECT Syntax
If you want to select all the columns from the source table, and copy it to the destination table, then the following syntax will work.
INSERT INTO destinationTableName (columnNames)
SELECT *
FROM sourceTableName
WHERE condition
If you want to select particular columns from the destination table, then the following syntax will work. Note that columnNames will be a list of columns separated by a comma.
INSERT INTO destinationTableName (columnNames)
SELECT columnNames
FROM sourceTableName
WHERE condition
3. INSERT INTO SELECT Example
We will be using the MySQL Command line to show how Insert Into Select statement is used. You can also do the same either by using the MySQL compiler available online or the MySQL Workbench. By using Insert Into Select statement, we will be copying data from source to destination table.
3.1 Creating two demo tables – source and destination
As an example, two tables have been created namely STUDENT, and VOLUNTEER. The STUDENT table consists of 3 columns that include the studentId, studentName, and country. Similarly, the VOLUNTEER table also comprises of 3 columns that include the volunteerId, volunteerName and country.
3.2 Inserting Data into the two tables
Some sample data has been inserted into the two tables STUDENT and VOLUNTEER using the insert command.
Note: If any mistake is made in the data types for the respective values, then it will result in an error and insertion will not be successful. So, make sure that the string is enclosed in single quotes.
Let us see how the data looks after inserting the rows into the tables. We have used the select command to fetch the data in both tables. You can see that there are 3 rows inserted in the student table successfully, and 3 rows in the volunteer table successfully as well.
3.3 Using the Insert Into Select Statement
Now, we are going to use the insert into a select statement to copy of details of Indian students in the student table to the volunteer table.
You can clearly see that before using the insert into a select statement, the volunteer table has 3 rows. When the Insert Into select statement is used, 1 row (which is the last row in the updated volunteer table) is added.
So, you can see that this statement has no effect either on the existing data in the destination table nor on the source table.
4. Summary
In this example, we learned:
- The functionality and usage of Insert Into Select statement.
- Implementing the functionality using MySQL Command Line Client.
5. Download the SQL Script
Download the SQL script comprising of the commands used in the example.
You can download the full source code of this example here: SQL INSERT INTO SELECT STATEMENT Example
Last updated on Nov. 07th, 2021
