sql

SQL INSERT INTO SELECT Statement Example

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.

SQL INSERT INTO SELECT - the tables student and volunteer
Fig 1: Creating the tables – student and volunteer

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.

SQL INSERT INTO SELECT - Data inserted
Fig 2. Data inserted in student and volunteer

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.

Fig 3. Data in the tables – student and volunteer

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.

SQL INSERT INTO SELECT - add the Indian student's details
Fig 4. Usage of Insert Into Select statement

4. Summary

In this example, we learned:

  1. The functionality and usage of Insert Into Select statement.
  2. 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.

Download
You can download the full source code of this example here: SQL INSERT INTO SELECT STATEMENT Example

Last updated on Nov. 07th, 2021

Simran Koul

Simran has graduated as a Bachelor of Engineering in Computer Science from Chitkara University. She has undergone a 6-months long comprehensive industrial training at the reputed Centre for Development of Advanced Computing (C-DAC), where she worked on a project including the likes of Java, JSP, Servlets while the UI-UX through the pioneering HTML, CSS and JS. Her inquisitive nature and the seed of curiosity keeps her on the toes to find material to write about. Along with her interests in Software Development, she is an ardent reader and always ready-to-write writer.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button