Duplicating a MySQL table, indexes and data

How do I copy / clone / duplicate the data, structure and indexes of a MySQL table to a new one?

This is what I've found so far.

This will copy the data and the structure, but not the indexes:

create table {new_table} select * from {old_table};

This will copy the structure and indexes, but not the data:

create table {new_table} like {old_table};

Answers

To copy with indexes and triggers do these 2 queries:

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

To copy just structure and data use this one:

CREATE TABLE tbl_new AS SELECT * FROM tbl_old;

I've asked this before:

Copy a MySQL table including indexes

---

MySQL Way

CREATE TABLE recipes_new LIKE production.recipes; INSERT recipes_new SELECT * FROM production.recipes;

---

I found the same situation and the approach which I took was as follows: 1. Execute SHOW CREATE TABLE : This will give you the Create Table syntax for the table which you want to clone 2. Run the CREATE TABLE query by changing the table name to clone the table.

This will create exact replica of the table which you want to clone along with Indexes. The only thing which you then need is to rename the indexes (if required).

---

FOR MySQL

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

FOR MSSQL Use MyDatabase:

Select * into newCustomersTable  from oldCustomersTable;

This SQL is used for copying tables, here the contents of oldCustomersTable will be copied to newCustomersTable.
Make sure the newCustomersTable does not exist in the database.