MySQL - INSERT INTO table using SELECT statement

In this tutorial we will learn to insert data into table using SELECT statement in MySQL.

We learned how to insert data into a table and how to select data from a table in the previous tutorials. Feel free to check that out.

INSERT INTO ... SELECT syntax

Following is the syntax to insert data into a table using the SELECT statement.

INSERT INTO table_name(column_name)
SELECT tbl_name.col_name
FROM some_table tbl_name
WHERE condition;

Where tbl_name is an alias for the table some_table.

Tables

In this tutorial we will be using the employeeaddress and projectemployee table.

Table: employeeaddress

mysql> SELECT * FROM employeeaddress;
+----+------------+-------------+--------------------------------+-----------------+-----------+-------+---------+---------+
| id | employeeid | addresstype | addressline1                   | addressline2    | city      | state | country | pincode |
+----+------------+-------------+--------------------------------+-----------------+-----------+-------+---------+---------+
|  1 | e01        | PRIMARY     | #12 street                     | Action block    | Bangalore | KA    | INDIA   | 560000  |
|  2 | e03        | PRIMARY     | Super Housing Complex Block 22 | Major Street    | Chennai   | TN    | INDIA   | 600000  |
|  3 | e03        | SECONDARY   | House #301 ABC Colony          | Jupiter Block   | Kolkata   | WB    | INDIA   | 700000  |
|  4 | e02        | PRIMARY     | Pizza Street                   | Foodies Block   | Kolkata   | WB    | INDIA   | 700000  |
|  5 | e04        | PRIMARY     | Fruits Apartment               | Fresh Block     | Bangalore | KA    | INDIA   | 560000  |
|  6 | e05        | PRIMARY     | Apartment 10                   | Fast Food Block | Chennai   | TN    | INDIA   | 600000  |
+----+------------+-------------+--------------------------------+-----------------+-----------+-------+---------+---------+
6 rows in set (0.00 sec)

So, there are total 6 address entry in the table.

Table: projectemployee

mysql> SELECT * FROM projectemployee;
Empty set (0.00 sec)

The projectemployee table is empty at the moment and we will be filling it up using data from the employeeaddress table.

Example

In the following example we will assign all the employees living in the city Bangalore to the project p01.

mysql> INSERT INTO projectemployee (projectid, employeeid)
SELECT 'p01', ea.employeeid
FROM employeeaddress ea
WHERE ea.city = 'Bangalore';

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

So, 2 rows were inserted into the projectemployee table.

We can check that using the select query.

mysql> SELECT * FROM projectemployee;
+-----------+------------+
| projectid | employeeid |
+-----------+------------+
| p01       | e01        |
| p01       | e04        |
+-----------+------------+
2 rows in set (0.00 sec)