Summary: In this post, we will learn to create a new table like another existing table using the CREATE TABLE statement in MySQL.
CREATE TABLE LIKE and
CREATE TABLE SELECT
The general syntax to create a table like an already existing table in MySQL is:
CREATE TABLE new_table LIKE original_table;
new_tableis the name of the new table and
original_tableis the name of the existing table.
The above MySQL statement creates an empty table based on the definition of the original table, including any column attributes and indexes defined in the original table.
In order to create a table based on another table,
SELECT privilege is required on the original table.
LIKEworks only for base tables, not for views.
Also, It is important to note that
CREATE TABLE ... LIKE will not create a temporary table irrespective of the fact whether the original table is a temporary table or not.
To create a TEMPORARY destination table, use:
CREATE TEMPORARY TABLE new_table LIKE original_table;
Another approach of creating a table from another is by adding a
SELECT statement at the end of the
CREATE TABLE statement:
CREATE TABLE new_table [AS] SELECT * FROM original_table [LIMIT 0];
Note: Syntax within square brackets is optional.
LIMIT 0 is used to create an empty table otherwise, all the data will also be copied along with the table definition.
CREATE TABLE ... SELECT and
CREATE TABLE ... LIKE statement in MySQL can create a new table from another table but the main difference between them is that
CREATE TABLE ... LIKE statement preserves the database objects such as indexes, primary key, foreign key constraints, triggers, etc., associated with the table.
Let’s see some examples in MySQL for better understanding.
MySQL CREATE TABLE LIKE examples
We will work on ‘pencil_db’ which currently has the following tables.
Example 1: Creating a new table ‘coding’ like ‘programming’ using
CREATE TABLE ... LIKE statement.
Query OK, 0 rows affected (0.73 sec)
CREATE TABLE coding LIKE programming;
If we look into the
SHOW CREATE TABLE coding; statement’s result we observe that various database objects such as foreign key constraints for the original table (‘programming’) are preserved to the destination table (‘coding’).
Example 2: Creating a new table ‘users’ like ‘students’ using
CREATE TABLE ... SELECT statement.
Query OK, 0 rows affected (1.46 sec)
CREATE TABLE users SELECT * FROM students LIMIT 0;
And if we look into the table we observe that table is empty.
Empty set (0.00 sec)
SELECT * FROM users;
Example 3: Creating a new table ‘persons’ like ‘students’ using
CREATE TABLE ... SELECT statement without
The original table ‘students’ has the following records.
Creating a new table ‘persons’.
Query OK, 1 row affected (1.19 sec)
CREATE TABLE persons SELECT * FROM students;
The ‘persons’ table has been successfully created and if we look into the table we observe that it has the same records as the ‘students’ table.
SELECT * FROM persons;
We should use
CREATE TABLE ... SELECTstatement only when the records of the original table need not to be transferred to new table along with the definitions.
In this tutorial, we learned how to create a new table like another existing table in MySQL using both
CREATE TABLE ... SELECT and
CREATE TABLE ... LIKE statements.