MYSQL insert select – MYSQL INSERT WITH SELECT

mysql insert select: In this article we are going to discuss how to insert data into a table using select method in MYSQL.

MYSQL INSERT WITH SELECT: SINGLE TABLE

Select method copies data from one table and inserts into another table.

Syntax:

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

Lets take an example,here we will create a table employees_regist ,which will then select some data from an already existing table emplyees_details in our database.

emplyees_details table has columns emloyee_no, first_name, last_name and designation_post. To see all the rows in employees_details table we will do a SELECT * query on it.

SELECT * FROM employee_details;

After this query you will get this result,

Output:

+-------------+------------+-----------+------------+
| employee_no | first_name | last_name | desig_post |
+-------------+------------+-----------+------------+
|                    1 | Shikha       | Mishra       | Developer |
|                    2 | Ritika         | Mathur      | Designer   |
|                    3 | Rohan        | Rathour     | Developer |
|                    4 | Aarush       | Rathour     | Developer |
|                    5 | Aadhya      | Roopam    | Techwriter |
+-------------+------------+-----------+------------+

So you can see that we already have one table(employee_details), now we are going to create our new table ’employee_regist’.

CREATE TABLE employee_regist(
    employee_exp INT AUTO_INCREMENT,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    employee_post VARCHAR(255),
    employee_salary INT,
    PRIMARY KEY (employee_exp)
);

So you can see that we have created a table but there is no value because our table is empty at this time,if you want to see table you can write select query,

SELECT * FROM employee_regist;

Output:

Empty set (0.05 sec)

Now we are going to add values in employee_regist.For this we are going to write INSERT query.

INSERT INTOemployee_regist(emploee_exp,first_name,last_name ) 
 SELECT employee_no,first_name,last_name FROM employee_details 
 WHERE desig_post = 'Developer' ;

Now to show output we will execute our SELECT query ,

SELECT * FROM registration_details;

Output:

+-------------+------------+-----------+------------+
| employee_no | first_name | last_name | desig_post |
+-------------+------------+-----------+------------+
|                    1 | Shikha       | Mishra       | Developer |
|                    3 | Rohan        | Rathour     | Developer |
|                    4 | Aarush       | Rathour     | Developer ||
+-------------+------------+-----------+------------+

So in above table you have seen that all table not added only those satisfying the given condition were addaed.

MYSQL INSERT WITH SELECT: MULTIPLE TABLES

In above table you have seen that we have added rows and column from single table but you can also do the same with multiple table.Let’s see it by an example,

We have employee_regist,emplyee_details and eregistered_employee already present in our database,

Now we are going to execute SELECT query to see the data in these table one by one,

’employee_regist’

SELECT * FROM employee_regist;

Output:

+-------------+------------+-----------+------------+
| employee_no | first_name | last_name | confirm     |
+-------------+------------+-----------+------------+
|                      |                    |                  |                   |
|                      |                    |                  |                   |
|                      |                    |                  |                   |
+-------------+------------+-----------+------------+

’employee_details’

SELECT * FROM employee_details;

output:

+-------------+------------+-----------+------------+
| employee_no | first_name | last_name | desig_post |
+-------------+------------+-----------+------------+
|                    1 | Shikha       |  Mishra      | Developer |
|                    2 | Ritika         | Mathur      | Designer   |
|                    3 | Rohan        | Rathour     | Developer |
|                    4 | Aarush       | Rathour     | Developer |
|                    5 | Aadhya      | Roopam    | Techwriter |
+-------------+------------+-----------+------------+

’employee_status’

SELECT * FROM registered_employee;

Output:

+-------------+------------+-----------+------------+
|  first_name | last_name | confirm                            |
+-------------+------------+-----------+------------+
|         Shikha | Mishra     | yes                                    |
|           Ritika | Mathur    | yes                                    |
|          Rohan | Rathour   | No                                    |
|          Aarush| Rathour   |yes                                     |
|        Aadhya | Roopam  | No                                     |
+-------------+------------+-----------+------------+

Now we are going to write a query in MySQL to get the data into employee_regist from  employee_details and registered_employee.

Selecting first_name and last_name from employee_details and confirm from employee_registered of only those students who have ‘Developer‘ stream.

INSERT INTO employee_regist(employee_no,first_name,last_name, employee_confirm) 
SELECT em.employee_no,em.first_name,em.last_name ,re.confirm
FROM  employee_details em , confirm re
WHERE em.first_name = re.first_name 
AND em.last_name = re.last_name
AND em.stream_name = 'Developer'  ;

Above in our query we have used em and re as alias,So now we are going to see output It will add data from multiple table.

Output:

+————-+————+———–+————+
| employee_no | first_name | last_name | confirm |
+————-+————+———–+————+
| 1                    | Shikha       | Mishra      |yes          |
| 3                    | Rohan       | Rathour     |No          |
| 4                    | Aarush      | Rathour     |yes          |

+————-+————+———–+————+

Conclusion:

In this article we have discussed how to insert data into a single and multiple table using select method in MYSQL.

Thanks to all!