Sometimes you may need to insert many values from one table into another table in MySQL. It can be tedious to manually enter these values in your INSERT sql statement. Here’s how to easily use SELECT statement to insert values into table with MySQL INSERT INTO SELECT query. You can use it to quickly populate MySQL tables.
MySQL Insert Into Select
Here are the steps to insert values in MySQL tables using INSERT INTO SELECT statement. Let’s say you have the following table new_orders(id, order_date, amount)
mysql> create table new_orders(id int, order_date date, amount int);
Here’s the syntax for MySQL INSERT INTO SELECT statement.
INSERT INTO table_name(column_list) SELECT select_list FROM another_table WHERE condition;
In the above query, we use a SELECT statement, instead of VALUES clause. It is very useful to quickly copy one or more rows from another table, or even summary data from another table.
Bonus Read : MySQL Select Top N Rows
Let us look at a few examples for MySQL INSERT INTO SELECT.
MySQL INSERT INTO SELECT MULTIPLE ROWS
Here’s the SQL query to insert multiple rows into a table using INSERT INTO SELECT statement.
mysql> insert into new_orders
select id, order_date, item_price
from orders;
mysql> select * from new_orders;
+------+------------+--------+
| id | order_date | amount |
+------+------------+--------+
| 1 | 2020-07-01 | 150 |
| 2 | 2020-07-01 | 235 |
+------+------------+--------+
You can omit the column names after INSERT clause if the column names in SELECT statement are identical to those in your table.
Bonus Read : MySQL Remove Duplicate Records
MySQL INSERT INTO SELECT FROM SAME TABLE
Here’s the SQL query to insert rows from same table using INSERT INTO SELECT statement.
mysql> insert into new_orders
select *
from new_orders;
mysql> select * from new_orders;
+------+------------+--------+
| id | order_date | amount |
+------+------------+--------+
| 1 | 2020-07-01 | 150 |
| 2 | 2020-07-01 | 235 |
| 1 | 2020-07-01 | 150 |
| 2 | 2020-07-01 | 235 |
+------+------------+--------+
In the above query, we use MySQL INSERT INTO SELECT for all columns in a table.
Bonus Read : How to Execute Stored Procedure
MySQL INSERT INTO SELECT from Another Database
Here’s the SQL query to insert rows from another database dashboard. The only difference is to prepend the database name to your table name in SELECT query.
mysql> insert into users (user_id,date_joined) select id,date_joined from dashboard.auth_user;
In the above query, we have used dashboard.auth_user instead of just auth_user to specify that this table is in a different database.
Bonus Read : MySQL Get Duplicate Records
MySQL INSERT INTO SELECT on duplicate update
If you insert duplicate primary key or index values in a table, MySQL will throw an error. In INSERT INTO SELECT statement, you can also instruct MySQL to update specific columns in case of duplicate values, using ON DUPLICATE KEY UPDATE clause. Here’s an example of MySQL INSERT INTO SELECT on duplicate update.
Let’s say you have the following table x_orders(id, amount)
mysql> create table x_orders(id int auto_increment primary key, amount int);
Let’s say you have new_orders table which has duplicate values for id column.
mysql> select * from new_orders; +------+------------+--------+ | id | order_date | amount | +------+------------+--------+ | 1 | 2020-07-01 | 150 | | 2 | 2020-07-01 | 235 | | 1 | 2020-07-01 | 150 | | 2 | 2020-07-01 | 235 | +------+------------+--------+
Let’s say you try to insert values from new_orders table into x_orders. You will get an error as shown.
mysql> insert into x_orders(id,amount)
select id, amount
from new_orders;
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
Here’s the SQL to use ON DUPLICATE KEY UPDATE clause.
mysql> insert into x_orders(id,amount)
select id, amount
from new_orders
on duplicate key update id = RAND() * 100;
mysql> select * from x_orders;
+----+--------+
| id | amount |
+----+--------+
| 27 | 235 |
| 48 | 150 |
| 58 | 235 |
| 62 | 150 |
+----+--------+
Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
