At work I use different software (and Windows) then at home (Linux). Today I needed to duplicate a live table before making alterations. I was needed in duplicating an entire table that retained the same attributes and properties with exactly the same rows and columns of data.

First of all you create a new table that contains an identical structure to the old one this is done with LIKE.

1

CREATE TABLE newtable LIKE oldtable

With the structure setup you simply copy the contents with an INSERT query that selects everything from the old table.

1

INSERT INTO newtable SELECT * FROM 'oldtable'

Very simple and useful to remember. When you’ve become accustomed to not having to this via a query it can be a real headache to forget!

1
2

CREATE TABLE newtable LIKE oldtable;
INSERT INTO newtable SELECT * FROM 'oldtable';

This entry was posted on Wednesday, October 21st, 2009 at 6:04 pm and is filed under Databases. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.