Create a table in MySQL that matches another table?

To create a table in MySQL that matches with another table, use CREATE TABLE command with LIKE operator. The syntax is as follows −

create table yourNewTableName like yourOldTableName;

The above syntax creates structure of the table.

If you want all records then use INSERT INTO…...SELECT *FROM command. The syntax is as follows −

insert into yourNewTableName select *from yourOldTableName.

I have an old table and some data −

mysql> create table WholeWordMatchDemo
   −> (
   −> Words varchar(200)
   −> );
Query OK, 0 rows affected (0.84 sec)

First, we will create a table structure. The query is as follows −

mysql> create table NewTableDuplicate Like WholeWordMatchDemo;
Query OK, 0 rows affected (0.62 sec)

Now you can check the table structure has been created or not with the help of show command. The query is as follows −

mysql> show create table NewTableDuplicate;

The following is the output −

+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Table             | Create Table                                                                                                                                |
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| NewTableDuplicate | CREATE TABLE `newtableduplicate` (`Words` varchar(200) DEFAULT NULL) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci |
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Copy all records in the new table with the name ‘NewTableDuplicate’. The query is as follows to copy all the data into new table −

mysql> insert into NewTableDuplicate select *from WholeWordMatchDemo;
Query OK, 3 rows affected (0.19 sec)
Records: 3 Duplicates: 0 Warnings: 0

Now you can check all records are present in the new table or not with the help of SELECT statement. The query is as follows −

mysql> select *from NewTableDuplicate;

The following is the output −

+----------------------+
| Words                |
+----------------------+
| My Name is John      |
| Carol                |
| My Name is Johnson   |
+----------------------+
3 rows in set (0.00 sec)

Check whether the old table has the same records or not −

mysql> select *from WholeWordMatchDemo;

The following is the output −

+----------------------+
| Words                |
+----------------------+
| My Name is John      |
| Carol                |
| My Name is Johnson   |
+----------------------+
3 rows in set (0.00 sec)

Rishi Rathor

Published on 11-Jan-2019 08:03:40

• Related Questions & Answers
• How to create a table in JDBC using another table?
• Updating a MySQL table with values from another table?
• MySQL select query to select rows from a table that are not in another table?
• Copy all rows of a table to another table in MySQL?
• Insert values in a table by MySQL SELECT from another table in MySQL?
• INSERT INTO table if a table exists in MySQL else implement CREATE TABLE and create the table
• Create MySQL query to create a table from an existing table?
• Can we add a column to a table from another table in MySQL?
• GROUP BY a column in another MySQL table
• Check that a table exists in MySQL?
• How can we create a new MySQL table by selecting specific column/s from another existing table?
• How to create a MySQL table with InnoDB engine table?
• How to create a MySQL table with MyISAM engine table?
• Update data in one table from data in another table in MySQL?
• Create index on create table in MySQL?