Let’s say you need to copy data from one table into another. You can insert data from table into another in MySQL.
The INSERT INTO SELECT statement copies data from one table to another. Existing rows in the target table are unaffected.
Syntax
Insert data from one table into another for all columns,
INSERT INTO table2 SELECT * FROM table1;
Insert data from one table into another for specific columns,
INSERT INTO table2 column1, column2.. SELECT column1, column2, .. FROM table1;
Examples
Consider 2 sample tables, Users and Customers
Users +------+--------------+-------------+ | id | user_name | location | +------+--------------+-------------+ | 1 | Jim | London | | 4 | Rocky | US | | 7 | Dan | Italy | | 3 | Bill | France | +------+--------------+-------------+
Customers +-------+------------------+--------+------------+ | id | customer_name | age | location | +-------+------------------+--------+------------+ | 11 | John | 23 | US | | 14 | Roger | 36 | London | | 17 | Will | 29 | Spain | | 13 | Bob | 34 | Japan | +-------+------------------+--------+------------+
Insert data from one table into another for a few columns
mysql> INSERT INTO users (user_name, location) SELECT customer_name, location FROM customers; +------+--------------+-------------+ | id | user_name | location | +------+--------------+-------------+ | 1 | Jim | London | | 4 | Rocky | US | | 7 | Dan | Italy | | 3 | Bill | France | | 11 | John | US | | 14 | Roger | London | | 17 | Will | Spain | | 13 | Bob | Japan | +-------+-------------+-------------+
Insert data from one table into another for a few columns, only those customers located in ‘US’
mysql> INSERT INTO users (user_name, location) SELECT customer_name, location FROM customers where location='US'; +------+--------------+-------------+ | id | user_name | location | +------+--------------+-------------+ | 1 | Jim | London | | 4 | Rocky | US | | 7 | Dan | Italy | | 3 | Bill | France | | 11 | John | US | +------+--------------+-------------+
