Summary: In this post, we will learn to create a new table like another existing table using the CREATE TABLE statement in MySQL.
Introduction to 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;
where
new_table
is the name of the new table andoriginal_table
is 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.
LIKE
works 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.
where LIMIT 0
is used to create an empty table otherwise, all the data will also be copied along with the table definition.
Both, 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.
CREATE TABLE coding LIKE programming;
Query OK, 0 rows affected (0.73 sec)
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.
CREATE TABLE users
SELECT * FROM students
LIMIT 0;
Query OK, 0 rows affected (1.46 sec)
And if we look into the table we observe that table is empty.
SELECT * FROM users;
Empty set (0.00 sec)
Example 3: Creating a new table ‘persons’ like ‘students’ using CREATE TABLE ... SELECT
statement without LIMIT 0
.
The original table ‘students’ has the following records.
Creating a new table ‘persons’.
CREATE TABLE persons
SELECT * FROM students;
Query OK, 1 row affected (1.19 sec)
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
LIMIT 0
withCREATE TABLE ... SELECT
statement 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.