Syntax error What is the MySQL SELECT INTO Equivalent?

What is the MySQL SELECT INTO Equivalent?

The SELECT INTO equivalent is CREATE TABLE AS SELECT statement. The syntax is as follows −

CREATE TABLE yourNewTableName AS SELECT *FROM yourTableName;

To understand the above concept, let us create a table. The query to create a table is as follows −

mysql> create table selectIntoEquivalentDemo
   -> (
   -> ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> ClientName varchar(20),
   -> ClientAge int
   -> );
Query OK, 0 rows affected (0.71 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into selectIntoEquivalentDemo(ClientName,ClientAge) values('Larry',34);
Query OK, 1 row affected (0.13 sec)
mysql> insert into selectIntoEquivalentDemo(ClientName,ClientAge) values('Maxwell',44);
Query OK, 1 row affected (0.06 sec)
mysql> insert into selectIntoEquivalentDemo(ClientName,ClientAge) values('Bob',38);
Query OK, 1 row affected (0.07 sec)
mysql> insert into selectIntoEquivalentDemo(ClientName,ClientAge) values('David',39);
Query OK, 1 row affected (0.09 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from selectIntoEquivalentDemo

Here is the output −

+----------+------------+-----------+
| ClientId | ClientName | ClientAge |
+----------+------------+-----------+
| 1        | Larry      | 34        |
| 2        | Maxwell    | 44        |
| 3        | Bob        | 38        |
| 4        | David      | 39        |
+----------+------------+-----------+
4 rows in set (0.00 sec)

The following is the query of SELECT INTO equivalent in MySQL −

mysql> create table Client_information AS select *from selectIntoEquivalentDemo;
Query OK, 4 rows affected (0.57 sec)
Records: 4 Duplicates: 0 Warnings: 0

Now let us check the table records from the new table. The query is as follows −

mysql> select *from Client_information;

Here is the output −

+----------+------------+-----------+
| ClientId | ClientName | ClientAge |
+----------+------------+-----------+
| 1        | Larry      | 34        |
| 2        | Maxwell    | 44        |
| 3        | Bob        | 38        |
| 4        | David      | 39        |
+----------+------------+-----------+
4 rows in set (0.00 sec)
Updated on: 2019-07-30T22:30:25+05:30

395 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements