MySQL: How to Duplicate a Table Tips, Tricks and Tutorials 10 SEP 2009

There exists in this world a nice little SQL statement know as the SELECT INTO statement, one that works beautifully well in most database systems for when you want to create a backup of an existing table. In MySQL however this doesn’t work straight out of the box, which of course is a pain in the ass.

So how does one duplicate this functionality in MySQL then?

Well funnily enough, it’s actually pretty damn simple. The CREATE TABLE IF NOT EXISTS phrase is key here and combining this with a select statement will in fact create a copy of your existing table, taking all the data from your source table and dumping it into your newly created clone table. (Note that you can control what data gets copied into the new table by modifying the SELECT statement with an appropriate WHERE statement as well as the columns that get created by specifying column names in the first part of the SELECT statement in place of the asterisk).

So for example: “CREATE TABLE IF NOT EXISTS `my_backup_table` SELECT * FROM `my_table`”

…will create the `my_backup_table` if it doesn’t exist and copy over all data currently contained in `my_table`.

Pretty useful, no?

It is however important to note that this process does come with a few drawbacks. Firstly, attribute data like primary keys, comments, etc. gets lost in the process. Also, don’t expect any associated triggers to make the trip either. Finally, certain default values like CURRENT_TIMESTAMP gets converted to 0000-00-00 00:00:00 as well, just to add insult to injury.

But then, if this backup table really is just about keeping the existing data safe, then I guess this really shouldn’t matter all that much to you in the first place! :)

a nest of wooden tables

About Craig Lotter

South African software architect and developer at Touchwork. Husband to a cupcake baker and father to two little girls. I don't have time for myself any more.