Insert the results of MySQL select? Is it possible?

No need to use values ​​whenever inserting selection results. In order to insert the results of select, we first create two tables.

The first table query is as follows -

< FirstTableDemo>
mysql> create table FirstTableDemo
   &minus;> (
   &minus;> StudentId int,
   &minus;> StudentName varchar(100)
   &minus;> );
Query OK, 0 rows affected (0.41 sec)

Now create the second table, and then use the INSERT SELECT command to insert the records of the second table into the first table.

The query to create the second table is as follows:

<SecondTableDemo>

mysql> create table SecondTableDemo
   &minus;> (
   &minus;> Id int,
   &minus;> Name varchar(100)
   &minus;> );
Query OK, 0 rows affected (0.47 sec)

Use the insert command to insert records in the table. The query is as follows −

mysql> insert into SecondTableDemo values(1,'John');
Query OK, 1 row affected (0.14 sec)

mysql> insert into SecondTableDemo values(2,'Sam');
Query OK, 1 row affected (0.27 sec)

Now there are two records in the second table. The query that uses the select statement to display all records in the table is as follows -

mysql> select *from SecondTableDemo;

The following is the output−

+------+------+
| Id   | Name |
+------+------+
|    1 | John |
|    2 | Sam  |
+------+------+
2 rows in set (0.00 sec)

Use the INSERT SELECT statement to insert all the records of the second table into the first table−

mysql> insert into FirstTableDemo(StudentId,StudentName)
   −> select Id,Name from SecondTableDemo as tbl1
   −> where tbl1.Id not in (select StudentId from FirstTableDemo);
Query OK, 2 rows affected (0.57 sec)
Records: 2 Duplicates: 0 Warnings: 0

Now we have inserted all the records from the second table into the first table. Let's cross check using select statement. The query is as follows −

mysql> select *from FirstTableDemo;

The following is the output −

+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
|         1 | John        |
|         2 | Sam         |
+-----------+-------------+
2 rows in set (0.00 sec)

The above is the detailed content of Insert the results of MySQL select? Is it possible?. For more information, please follow other related articles on the PHP Chinese website!