Select into in MySQL?

MySQLMySQLi Database

To do select into in MySQL, use CREATE TABLE SELECT command. The syntax is as follows −

CREATE TABLE yourTableName SELECT *FROM yourOriginalTableName;

To understand, let us first create a table −

mysql> create table SelectIntoDemo
-> (
-> Id int,
-> Name varchar(200)
-> );
Query OK, 0 rows affected (0.50 sec)

Let us insert some records into the table with the help of insert command. The query is as follows −

mysql> insert into SelectIntoDemo values(1,'Bob'),(2,'Carol'),(3,'David');
Query OK, 3 rows affected (0.15 sec)
Records: 3 Duplicates: 0 Warnings: 0

Displaying all records with the help of select statement. The query is as follows −

mysql> select *from SelectIntoDemo;

Here is the output −

+------+-------+
| Id   | Name |
+------+-------+
| 1    | Bob   |
| 2    | Carol |
| 3    | David |
+------+-------+
3 rows in set (0.00 sec)

Now you can apply the above syntax discussed in the beginning. The query is as follows −

mysql> create table yourTempTable select *from SelectIntoDemo;
Query OK, 3 rows affected (0.56 sec)
Records: 3 Duplicates: 0 Warnings: 0

Check all records with new table name “yourTempTable”. The query is as follows −

mysql> select *from yourTempTable;

The following is the output −

+------+-------+
| Id   | Name  |
+------+-------+
| 1    | Bob   |
| 2    | Carol |
| 3    | David |
+------+-------+
3 rows in set (0.00 sec)
raja
Published on 24-Dec-2018 07:04:31
Advertisements