How to Create Table Like Another Table in MySQL?

create table like another table

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 and
  • original_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.

pencil_db database 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)

coding table created

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’).

show create 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)

users table created

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.

students table records

Creating a new table ‘persons’.

CREATE TABLE persons
SELECT * FROM students;
Query OK, 1 row affected (1.19 sec)

persons table created

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;
persons table records

We should use LIMIT 0 with CREATE 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.

Leave a Reply

Your email address will not be published. Required fields are marked *