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

Updated on 30-Jul-2019 22:30:24

• Related Questions & Answers
• Select and insert values with preceding zeros in a MySQL table
• Insert values in a table by MySQL SELECT from another table in MySQL?
• Insert record using MySQL SELECT?
• Combine SELECT & SHOW command results in MySQL?
• Insert with a Select query in MySQL
• Copy values of one column to another using INSERT and SELECT in a single MySQL query
• MySQL Select Multiple VALUES?
• Best way to combine multiple advanced MySQL select queries?
• MySQL select and insert in two tables with a single query
• Perform multiple inserts with INSERT INTO SELECT and UNION in MySQL
• Can we implement nested insert with select in MySQL?
• MySQL randomly select 2 values from column values?
• How to SELECT fields from one table and INSERT to another in MySQL?
• Insert row with only default values in MySQL
• MySQL Select where values IN List string?