CODEWITHSUNDEEP
mysql
Matthew
Matthew

Reputation: 7725

Copy table without copying data

CREATE TABLE foo SELECT * FROM bar

copies the table foo and duplicates it as a new table called bar.

How can I copy the schema of foo to a new table called bar without copying over the data as well?

Upvotes: 211

Views: 177073

Answers (5)

Tatu IoT
Tatu IoT

Reputation: 11

I´d extend the answer to

CREATE TABLE DB.TABLE_copy LIKE DB.TABLE;

depending on the case, you could be more explicit to avoid any mistake. Thanks RCNeil.

Upvotes: 0

Ali Azaz Alam
Ali Azaz Alam

Reputation: 1868

Only want to clone the structure of table:

CREATE TABLE foo SELECT * FROM bar WHERE 1 = 2;

Also wants to copy the data:

CREATE TABLE foo as SELECT * FROM bar;

Upvotes: 2

RCNeil
RCNeil

Reputation: 8759

Try 

CREATE TABLE foo LIKE bar;

so the keys and indexes are copied over as, well.

Documentation

Upvotes: 527

Timo Huovinen
Timo Huovinen

Reputation: 55613

SHOW CREATE TABLE bar;

you will get a create statement for that table, edit the table name, or anything else you like, and then execute it.

This will allow you to copy the indexes and also manually tweak the table creation.

You can also run the query within a program.

Upvotes: 35

Andomar
Andomar

Reputation: 238068

Try:

CREATE TABLE foo SELECT * FROM bar LIMIT 0

Or:

CREATE TABLE foo SELECT * FROM bar WHERE 1=0

Upvotes: 168

Related Questions