MySQL Tutorial – Like clause
Like clause in a CREATE TABLE command will create an exact copy of your existing table. The newly copied or created table will also acquire the column definition or attributes of the original one.
Syntax of like clause:
CREATE TABLE nameOfNewTable LIKE nameOfExistingTable;
Where:
nameOfNewTable – the name of the new table to be created.
nameOfExistingTable – the name of the existing table in your database.
Example: we want to create a copy of our employee table and named it as staff. Issue the command:
CREATE TABLE staff LIKE employee;
Note: this command will only copy the structure of the original or existing table into the newly create table, meaning the records/rows from the original table will not be copied to the new one.
Copying the records from one table to another
After we have created an exact copy of our original table, it’s time to copy the existing records or data to the newly created table. Issue the command:
INSERT INTO staff SELECT * FROM employee;
A success message will display after you have encoded the command:
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
Note: this command is only applicable if the two tables are identical, which means that the two tables have the same field and those field have the same attributes, otherwise you will encounter an error.
