Insert record using MySQL SELECT?

To insert a record with select, use INSERT INTO SELECT statement. Let us first create a table −

mysql> create table DemoTable1
   -> (
   -> Id int,
   -> FirstName varchar(20)
   -> );
Query OK, 0 rows affected (0.71 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1 values(101,'Chris');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable1 values(NULL,'Bob');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable1 values(NULL,NULL);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1 values(102,'David');
Query OK, 1 row affected (0.20 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable1;

This will produce the following output −

+------+-----------+
| Id   | FirstName |
+------+-----------+
|  101 | Chris     |
| NULL | Bob       |
| NULL | NULL      |
|  102 | David     |
+------+-----------+
4 rows in set (0.00 sec)

Here is the query to create the second table.

mysql> create table DemoTable2
   -> (
   -> StudentId int,
   -> StudentName varchar(20)
   -> );
Query OK, 0 rows affected (1.10 sec)

Here is the query to insert record using SELECT statement −

mysql> insert into DemoTable2
   -> select Id,FirstName from DemoTable1
   -> where Id IS NOT NULL and FirstName IS NOT NULL;
Query OK, 2 rows affected (0.17 sec)
Records: 2 Duplicates: 0 Warnings: 0

Display all records from the table using select statement −

mysql> select *from DemoTable2;

This will produce the following output −

+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
|       101 | Chris       |
|       102 | David       |
+-----------+-------------+
2 rows in set (0.00 sec)

AmitDiwan

Published on 13-Dec-2019 06:08:26

• Related Questions & Answers
• Insert record in a MySQL table with Java
• Select last record and update it in MySQL?
• MySQL select * and find record with current date
• How to select first and last row record using LIMIT in MySQL?
• Combine INSERT, VALUES, and SELECT in MySQL
• Insert with a Select query in MySQL
• Get the new record key ID from MySQL insert query?
• How to select record from last 6 months in a news table using MySQL?
• How to select record except the lower value record against a specific value in MySQL?
• MySQL Select when a grouped record has multiple matching strings?
• MySQL query to select a record with two exact values?
• How to select a random record from a MySQL database?
• Can we implement nested insert with select in MySQL?
• MySQL INSERT INTO SELECT into a table with AUTO_INCREMENT
• Can we select second largest record from a table without using LIMIT clause in MySQL?