EN
MySql - insert data from one table to another
13
points
In this article, we would like to show how to insert data from one table to another in MySQL.
Quick solution:
INSERT INTO `dst_table` (SELECT * FROM `src_table`)
Where:
src_table
means source table name,dst_table
means destination table name.
Practical examples
In this section, we want to show how to copy data with INSERT ... SELECT
query.
1.1. Copping selected columns
Only selected columns are copied with created one value ('copied'
).
INSERT INTO `dst_table` (`name`, `status`, `source_id`)
SELECT `name`, 'copied', `id`
FROM `src_table`
ORDER BY `name` ASC
1.2. Copping all columns
To use this approach both tables should have similar columns.
INSERT INTO `dst_table` (SELECT * FROM `src_table`);
or
INSERT INTO `dst_table` (SELECT * FROM `src_database`.`src_table`);
Note: use
src_database
only if source table is located in different database.