INSERT……….SELECT
INSERT………SELECT is used to quickly insert number or records to a table from
other table or tables .
EX:
Lets us take you have a table “users”
as
+———–++———–+
| uid | name
+———–++———–+
| 1 | name1
+———–++———–+
| 2 | name2
+———–++———–+
And if you want to transfer all the data to “users_bck”
The query can be written as
INSERT INTO users_bck (uid,name)
SELECT uid,name
FROM users ;
The following conditions hold for a INSERT … SELECT statements:
* Specify IGNORE to ignore rows that would cause duplicate-key violations.
* DELAYED is ignored with INSERT … SELECT.
* The target table of the INSERT statement may appear in the FROM clause of the SELECT part of the query. (This was not possible in some older versions of MySQL.) In this case, MySQL creates a temporary table to hold the rows from the SELECT and then inserts those rows into the target table.
* AUTO_INCREMENT columns work as usual.
* To ensure that the binary log can be used to re-create the original tables, MySQL does not allow concurrent inserts for INSERT … SELECT statements.
* Currently, you cannot insert into a table and select from the same table in a sub query.
