Google Ad Slot: content-top

MySQL INSERT SELECT

MySQL `INSERT ... SELECT` Statement

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

Search

Welcome Back

Create Account

Forgot Password

MySQL INSERT SELECT