In previous articles we learned to insert data into a table, feel free to checkout the article MYSQL INSERT INTO.

This article will discuss how to insert data into a table by selecting the data from some other source, for example, another table.

Table of Contents


Let us start by looking into the syntax.


INSERT INTO tableA (colA1,colA2,colA3…) 
SELECT colB1,colB2,colB3… from tableB   
WHERE condition;
  • Here, tableA is the table where we will add rows for column list colA1,colA2,colA3….
  • tableB is another table for selection from columns colB1,colB2,colB3…
  • WHERE clause is not mandatory, we can always add a condition if we want some specific, condition-based data.


To have a better understanding of the concept, we will work through an example. In this example, we will be creating a table registration_details, which will then select some data from an already existing table student_details in our database.

student_details table has columns student_no, first_name, last_name and stream_name. To see all the rows in student_details table we will do a SELECT * query on it.

SELECT * FROM student_details;



We can see there are 8 rows of data with five students having ‘Science’ stream. Now, let us create our new table registration_details.

CREATE TABLE registration_details (
    student_no INT AUTO_INCREMENT,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    registration_flag VARCHAR(10) DEFAULT 'ND',
    PRIMARY KEY (student_no)

If we do a select * from registration_details, it shows that the table is empty.

SELECT * FROM registration_details;

Output :-


We will proceed by adding rows to registration_details table, we are selecting a few columns (student_no, first_name, last_name) from student_details table. The example scenario is as follows:  registration_details table requires students who belong to stream ‘Science.’ The MySQL query will be :

 INSERT INTO registration_details(student_no,first_name,last_name ) 
 SELECT student_no,first_name,last_name FROM student_details 
 WHERE stream_name = 'Science' ;

Select * on registration_details table to view the added rows.

SELECT * FROM registration_details;

Output :-


Note that only the columns student_no, first_name and last_name have been added. Since our selection was based on a condition, only records satisfying the condition( students with stream_name ‘Science’ ) were added.


Like adding rows into a table from a single table, we can also select data from multiple tables.

Example :-

Again, let us understand with the help of an example. We will use table registration_details to add the data from tables student_details and registration_status (already present in the database).

Do a select * on all the three tables one by one to see what data is present in them.

SELECT * FROM registration_details;



Please note that we had truncated data from registration_details at the moment. Select * on student_details.

SELECT * FROM student_details;
SELECT * FROM registration_status;



We will now write a query in MySQL to get the data into registration_details from tables student_details and registration_status.

Selecting first_name and last_name from student_details and registration_flag from registration_status of only those students who have ‘Science‘ stream.

INSERT INTO registration_details(student_no,first_name,last_name, registration_flag) 
SELECT sd.student_no,sd.first_name,sd.last_name ,rs.registration_flag 
FROM  student_details sd , registration_status rs
WHERE sd.first_name = rs.first_name 
AND sd.last_name = rs.last_name
AND sd.stream_name = 'Science'  ;

The above query is a bit complex as we want to make sure that the correct status of registration_flag is loaded, and hence we are matching the first_name and last_ name of both the tables student_details and registration_status. Also, note that sd and rs here are aliases for student_details and registration_status tables, respectively.

Once the query is executed, we will look into the records of registration_details to make sure that we added the rows from more than one table.

SELECT * FROM registration_details;

Output :-


So, the rows have been added to registration_details successfully.

In this article we learned how to insert data to a table making selections from one or more than one tables. We hope this article helped you to have a better understanding on the concept of INSERT SELECT FROM.

Leave a Comment

Your email address will not be published. Required fields are marked *

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

Scroll to Top