Combine INSERT, VALUES, and SELECT in MySQL

You can combine the insert, values and select statement using below syntax

insert into yourFirstTableName(yourColumnName1,yourColumnName2,.......N)
select yourColumnName1,yourColumnName2,.......N
from yourSecondTableName where yourCondition;

To understand the above syntax, let us create two tables in which first table will get the record from the second table.

Let us create the first table without any records. The query to create a table is as follows

mysql> create table CombiningInsertValuesSelect
   -> (
   -> EmployeeId varchar(10),
   -> EmployeeName varchar(100),
   -> EmployeeAge int
   -> );
Query OK, 0 rows affected (6.95 sec)

Now you can create the second table with some records. The query to create a table is as follows

mysql> create table getAllValues
   -> (
   -> Id varchar(100),
   -> Name varchar(100),
   -> Age int
   -> );
Query OK, 0 rows affected (1.12 sec)

Insert records in the second table with the name ‘getAllValues’ using insert command. The query is as follows

mysql> insert into getAllValues values('EMP-1','John',26);
Query OK, 1 row affected (0.86 sec)

mysql> insert into getAllValues values('EMP-2','Carol',22);
Query OK, 1 row affected (0.36 sec)

mysql> insert into getAllValues values('EMP-3','Sam',24);
Query OK, 1 row affected (0.28 sec)

mysql> insert into getAllValues values('EMP-4','David',27);
Query OK, 1 row affected (0.25 sec)

mysql> insert into getAllValues
values('EMP-5','Bob',21);
Query OK, 1 row affected (0.75 sec)

Now you can display all records from the table using select statement. The query is as follows

mysql> select *from getAllValues;

The following is the output

+-------+-------+------+
| Id    | Name  | Age  |
+-------+-------+------+
| EMP-1 | John  |   26 |
| EMP-2 | Carol |   22 |
| EMP-3 | Sam   |   24 |
| EMP-4 | David |   27 |
| EMP-5 | Bob   |   21 |
+-------+-------+------+
5 rows in set (0.00 sec)

Here is the use of insert, values and select in MySQL. The query is as follows

mysql> insert into CombiningInsertValuesSelect(EmployeeId,EmployeeName,EmployeeAge)
   -> select Id,Name,Age from getAllValues where Id='EMP-4';
Query OK, 1 row affected (0.23 sec)
Records: 1 Duplicates: 0 Warnings: 0

Now check the record is present in the table or not using select statement. The query is as follows

mysql> select *from CombiningInsertValuesSelect;

The following is the output

+------------+--------------+-------------+
| EmployeeId | EmployeeName | EmployeeAge |
+------------+--------------+-------------+
| EMP-4      | David        | 27          |
+------------+--------------+-------------+
1 row in set (0.00 sec)

Ankith Reddy

Published on 16-Jan-2019 12:35:14

Previous Page Print Page