Insert data from one table into another in MySQL

Last updated on September 11th, 2020 at 11:26 am

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