We can insert data from one table to another in MySQL. For this we use the INSERT INTO SELECT statement that selects the data from on table and inserts that data into another table.

INSERT INTO SELECT Syntax

We can select and insert all the columns from one table to another table:

INSERT INTO table2
SELECT * FROM table1;

Or we can select and insert only the columns we want to insert into another table:

INSERT INTO table2
(column_names)
SELECT column_names
FROM table1;

Example:

We have two tables with the following values.

    user

id  user_name     country   
.......................................
1   John           Canada     
2   Chris          America        
3   Joy            London         
4   Jenny          Korea                              

    student

id  student_name     country   
.......................................
1   Rony             Canada     
2   Bonney           USA                
3   Kat              Mexico 

SQL INSERT INTO SELECT Examples

Insert only a few columns from "student" into "user":

Example

INSERT INTO user (user_name, Country)
SELECT student_name, country FROM student;

Result

user

id  user_name     country   
.......................................
1   John           Canada     
2   Chris          America        
3   Joy            London         
4   Jenny          Korea
5   Rony           Canada     
6   Bonney         USA                
7   Kat            Mexico  

Insert only the student who has country as USA into "user":

Example

INSERT INTO user (user_name, Country)
SELECT student_name, country FROM student
WHERE country='USA';

Result

user

id  user_name     country   
.......................................
1   John           Canada     
2   Chris          America        
3   Joy            London         
4   Jenny          Korea   
5   Bonney         USA