Create MySQL query to create a table from an existing table?

You can use CREATE TABLE command to create a table from an existing table. The syntax is as follows:

CREATE TABLE yourNewTableName LIKE yourOldTableName

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

mysql> create table ShowCreateTableCommand
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> FirstName varchar(20),
   -> LastName varchar(20),
   -> Age int,
   -> TotalMarks int,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (1.22 sec)

Here is the query to describe a table using DESC command. The query is as follows:

mysql> desc ShowCreateTableCommand;

The following is the output:

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| Id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| FirstName  | varchar(20) | YES  |     | NULL    |                |
| LastName   | varchar(20) | YES | | NULL | |
| Age | int(11) | YES | | NULL | |
| TotalMarks | int(11) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.11 sec)

Here is the query to check the structure of a table:

mysql> show create table ShowCreateTableCommand\G

The following is the output:

*************************** 1. row ***************************
Table: ShowCreateTableCommand
Create Table: CREATE TABLE `showcreatetablecommand` (
   `Id` int(11) NOT NULL AUTO_INCREMENT,
   `FirstName` varchar(20) DEFAULT NULL,
   `LastName` varchar(20) DEFAULT NULL,
   `Age` int(11) DEFAULT NULL,
   `TotalMarks` int(11) DEFAULT NULL,
   PRIMARY KEY (`Id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Let us now create a new table from an existing table using CREATE TABLE command. The query is as follows:

mysql> CREATE TABLE StudentInformationDemo LIKE ShowCreateTableCommand;
Query OK, 0 rows affected (0.49 sec)

Now check whether the structure of new table is matching with old table structure:

mysql> show create table StudentInformationDemo\G

The following is the output:

*************************** 1. row ***************************
Table: StudentInformationDemo
Create Table: CREATE TABLE `studentinformationdemo` (
   `Id` int(11) NOT NULL AUTO_INCREMENT,
   `FirstName` varchar(20) DEFAULT NULL,
   `LastName` varchar(20) DEFAULT NULL,
   `Age` int(11) DEFAULT NULL,
   `TotalMarks` int(11) DEFAULT NULL,
   PRIMARY KEY (`Id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Ankith Reddy

Published on 01-Feb-2019 17:19:23