Copying a MySQL table with schema and keys, but not data
Here's a great thing I learned about MySQL today:
SQL:
-
CREATE TABLE newtable LIKE oldtable
Will create a copy of the table along with structure and keys (primary keys, auto increments etc) but not the data.
This is perfect solution to create new tables based on an existing one - we use it to create a new database for every customer that signs up on a project. We go in a loop of the list of tables, and use the CREATE TABLE statement to duplicate the table in a new name. This works across databases too (assuming you have permissions).
Of course, if you wanted to get all the data, you can do something like:
SQL:
-
CREATE TABLE newtable LIKE oldtable;
-
INSERT INTO newtable SELECT * FROM oldtable;
The full CREATE TABLE syntax is here.
Related posts:
- Recursively unlock files on Mac OS X I copied some songs from a CD and ended up...
- Intelligent Time Duration / Estimate Slider in Flex (inspired from The Hit List) I wrote yesterday that I am working on a time...
- Bogged Down, Need a Break, Here’s what I am going to do this weekend I am bogged down. Got hundreds of things to do....
- Avoid these customers – five warning signs to say no to customers, plus three signs to die for Are you feeling the heat of times? There aren't...
A point to note is that you can crate a new table with indexes AND data in a single statement.
CREATE TABLE new_collection (ID INT auto_increment primary key)
SELECT artist, title, year FROM collection
This will be useful when you write a standard create table statement and add dummy data from another table. For e.g.
CREATE TABLE mynew
( PRIMARY KEY (nation), KEY fsearchIp (searchip)
) ENGINE = MEMORY DEFAULT CHARACTER SET utf8
SELECT * FROM world
Shantanu Oak
12 Oct 07 at 3:12 pm
Hi! Another nice way to get the table structure schema is to do: SHOW CREATE TABLE mytable;
Nice post, cheers!
Alix Axel
18 Oct 07 at 2:40 am
Your post helped me much. I was using “CREAT TABLE newtable SELECT * FROM oldtable” which correctly creates a table with data and structure of first table but it does not copy the indexes. This problem solved by your post.
Many Thanks.
Khurram
21 Feb 08 at 4:54 pm
Thanks!!!
That solve a problem I had.
Cheers!
Guillo
1 Dec 09 at 8:05 pm