Is there a way to copy the structure of a table (fields but no data) to a new table in MySQL?

Question

For example, I have a table named movies. It has the fields/columns title VARCHAR(100) and runtime INT(5). It's loaded with 10,000 rows of data.

I want to create another table, let's call it movies_custom, that has all of the same columns, but with none of the data.

Is there a quick SQL statement to do this?

Did you try http://stackoverflow.com/questions/766928/mysql-duplicate-table — user2330678, Oct 11 '13 at 21:10

score 1 · Answer 1 · answered Oct 11 '13 at 20:28

EDIT: Sorry, I noticed the SQL Server tag on this and assumed that was the technology you were using until I saw MySQL in the question title.

You can use the syntax CREATE movies_custom LIKE movies in MySQL

Sure!

In SQL Server you can do this query:

select top 0 * into movies_custom from movies

That creates the table structure without inserting any rows.

score 1 · Answer 2 · answered Oct 11 '13 at 20:30

1

Very easy in MySQL:

CREATE newtable LIKE oldtable;

answered Oct 11 '13 at 20:30

score 0 · Answer 3 · answered Oct 11 '13 at 20:40

0

The other answers led me in the right direction, but I had to add the keyword TABLE in order to make it work with MySQL Workbench 6.

CREATE TABLE movies_custom LIKE movies;

answered Oct 11 '13 at 20:40

Ethan Allen

14,425
24
101
194

Is there a way to copy the structure of a table (fields but no data) to a new table in MySQL?

3 Answers3