Insert data from one table into another in MySQL


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      |
+------+--------------+-------------+
mm

About Sreeram Sreenivasan

Sreeram Sreenivasan is the Founder of Ubiq, a business dashboard & reporting platform for small & medium businesses. Ubiq makes it easy to build business dashboards & reports for your business. Try it for free today!