Combining INSERT, VALUES, and SELECT in MySQL

You can combine insert, value and select statements using the following 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 where the first table Records will be fetched from the second table.

Let's create the first table without any records. The query to create the 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 a second table containing some records. The query to create the table is as follows

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

Use the insert command to insert a record named "getAllValues" in the second table. 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 use the select statement to display all records in the table. 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 use the select statement to check whether the record exists in the table. 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)

The above is the detailed content of Combining INSERT, VALUES, and SELECT in MySQL. For more information, please follow other related articles on the PHP Chinese website!