CODEWITHSUNDEEP
mysql
Hari kanna
Hari kanna

Reputation: 2521

Mysql query to copy the structure of a table to create another table

Looking for help in creating a Mysql query to copy the structure of an existing table to create another table.

Upvotes: 25

Views: 43261

Answers (6)

Aa.mbi
Aa.mbi

Reputation: 19

also you can use this:

CREATE TABLE 'new_table_name' SELECT * FROM 'pattern_table' where 1=0;

Upvotes: 1

krishnaisdinesh
krishnaisdinesh

Reputation: 391

Its old thread but if someone needed. If you want create query that will create exactly copy of existing table.

show create table <existing table name>

Upvotes: 4

Sonpal singh Sengar
Sonpal singh Sengar

Reputation: 247

MySQL query to copy the structure of a table to create another table structure without data another way is...

CREATE TABLE `table_name_new` select * from `table_name_old` limit 0;

Upvotes: 1

Hammerite
Hammerite

Reputation: 22340

To create a table as in an exact replica of another table:

CREATE TABLE `new_table_name` LIKE `old_table_name`;

Upvotes: 43

Kamal Kumar
Kamal Kumar

Reputation: 3763

If you want to copy the table structure including its keys, then you should use:

CREATE TABLE `new_table_name` LIKE `old_table_name`;

To copy the whole table

CREATE TABLE `new_table_name` SELECT * FROM `old_table_name`;

It will create the table and insert all the data from the old table but without bringing the keys from the old table. So you will need to set the keys for the new table.

Upvotes: 8

Monty
Monty

Reputation: 1322

If you want to also copy the contents of the table you can do:

CREATE TABLE `new_table_name` LIKE `old_table_name`;
INSERT INTO `new_table_name` SELECT * FROM `old_table_name`;

Upvotes: 19

Related Questions