How to create a copy of a MySQL table#

Probably you figured out how to backup and create a copy of your whole database already. But that's not what we wanna do all the time, sometimes you just wanna create a copy of a certain table before you experiment and do stuff on the target table.

Before we start copying the whole table, probably there is another requirement you might have. Maybe you don't wanna create a copy of the table with all its contents, maybe you just wanna backup the table structure aka schema. Let me show you how to do both of them.

Create a copy of a MYSQL table without its contents / data#

mysql> create table new_students like students

That command will create a new table called 'new_students' which is exactly like the existing table called 'students', except it is empty.

Create a copy of a MYSQL table with its contents / data#

mysql> create table new_students like students
mysql> insert into new_students (id, name, score) select id, name, score from students;

There you go! Essentially first you create an empty table called 'new_students' with the same schema as 'students' and then fill the new table with contents from the old table. 'id', 'name', 'score' are the fields in the 'students' table.

The shortcut way of doing what we just did is give below.

mysql> insert into new_students select * from students;

This way everything is copied onto the new table assuming the field names are similar.

You can use mysqldump for doing something similar but I find it a very tedious process - going to the shell prompt etc. For creating tables copies, I prefer doing it right from the MySQL prompt, like a boss!

Exercises#

  1. How do you create a copy of a table using mysqldump?
  2. What is the advantage of specifying field names while filling up the new table?
Tweet this | Share on LinkedIn |