Why is the INSERT INTO SELECT statement used?
If we want to copy data selected from one table to another, we can use MySQL INSERT INTO SELECT Statement.
Note that the data types we select from the table to the main table should match.
The syntax for INSERT INTO SELECT Statement:
INSERT INTO to_table
SELECT * FROM from_table
WHERE 1=1;
We have the table named ‘students’ shown below.
We will create one ‘students_dummy’ table having same columns as ‘students’ table and will insert the data from ‘students’ table to ‘students_dummy’ table.
Create Statement:
CREATE TABLE `students_dummy`(
`roll_num` int NOT NULL,
`name` varchar(45) DEFAULT NULL,
`marks` int NOT NULL,
`class` int DEFAULT NULL,
`gender` varchar(1) DEFAULT NULL
);
Example for INSERT INTO statement.
Inserting all data from ‘students’ table to ‘students_dummy’ table.
INSERT INTO students_dummy SELECT * FROM students;
Inserting some data from ‘students’ table to ‘students_dummy’ table using WHERE condition.
INSERT INTO students_dummy SELECT * FROM students WHERE roll_num < 5;
We can also add brackets around the select statement like below.
INSERT INTO students_dummy (SELECT * FROM students WHERE roll_num < 5);
We can also change the insert order in the table by using the order by clause below
INSERT INTO students_dummy (SELECT * FROM students WHERE roll_num < 5 ORDER BY roll_num DESC);
If we want to insert some columns then we can write the query as follows.
INSERT INTO students_dummy (roll_num, name, marks)
(SELECT roll_num, name, marks FROM students
WHERE roll_num < 5 ORDER BY gender DESC);
