Wednesday, November 19, 2008

Duplicating tables and problem with the structure

To duplicate a table:
mysql> CREATE TABLE products SELECT * FROM old_products;

But the problem with above statement is structure of products may not be the same as old_products. Attribute properties are not copied to the new table. For example of you have an auto_increment field named id, the auto_increment property won't be copied to products table. will be a normal integer field with the default value set to 0. It also means the new id field won't be the primary field. Took me 3 hours to figure this out.

So the best way to duplicate a table is:
1. CREATE TABLE products LIKE old_products;
2. INSERT INTO products SELECT * FROM old_products;

No comments: