MySQL
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.
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.
tbl_name
some_table
In this tutorial we will be using the employeeaddress and projectemployee table.
employeeaddress
projectemployee
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.
In the following example we will assign all the employees living in the city Bangalore to the project p01.
Bangalore
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)