MySQL INSERT SELECT: Definition, Syntax, and Examples

The INSERT ... SELECT statement in MySQL allows you to insert data into a table by selecting data from one or more tables. It is a powerful way to copy or migrate data without manually inserting each record.

INSERT INTO SELECT Syntax

Copy only some columns from one table into another table:

INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;

Copy all columns from one table to another table:

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

Explanation:

target_table → The table where data will be inserted.
column1, column2, ... → The columns of the target table that will receive data.
source_table → The table from which data is selected.
WHERE condition → (Optional) Specifies which rows should be inserted.

MySQL INSERT INTO SELECT Examples

We have two tables:

students Table

student_id	name	gender	city	age	score	course_id	email	phone_number
1	Alice	Female	Delhi	20	85	101	NULL	1234567890
2	Bob	Male	Mumbai	22	75	NULL	bob@email.com	NULL
3	Charlie	Male	Delhi	21	95	102	NULL	NULL
4	David	Male	Bangalore	23	65	101	david@email.com	NULL
5	Eve	Female	Mumbai	20	80	103	NULL	NULL
6	Frank	Male	Delhi	22	90	103	NULL	NULL
7	Alice	Female	Mumbai	19	60	102	NULL	NULL

top_students Table

student_id	name	score

This table is empty and will store students who scored above 80.

The following SQL statement copies "students" into "top_students" (the columns that are not filled with data, will contain NULL):

Example

INSERT INTO top_students (student_id, name, score)

SELECT student_id, name, score

FROM students

WHERE score > 80;

Output:

student_id	name	score
1	Alice	85
3	Charlie	95

Explanation:

The query selects students from the students table where their score is greater than 80.
The selected records are inserted into the top_students table.

The following SQL statement copies "students" into "top_students" (fill all columns):

Example

INSERT INTO top_students

SELECT *

FROM students

WHERE score > 80;

❮ Previous

Next ❯

Whereisstuff is simple learing platform for beginer to advance level to improve there skills in technologies.we will provide all material free of cost.you can write a code in runkit workspace and we provide some extrac features also, you agree to have read and accepted our terms of use, cookie and privacy policy.

MySQL INSERT ... SELECT Statement

INSERT INTO SELECT Syntax

Explanation:

MySQL INSERT INTO SELECT Examples

students Table

top_students Table

Output:

Explanation:

MySQL `INSERT ... SELECT` Statement