- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- 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?