Create table in MySQL that matches another table?

I am using MySQL. I have a table called EMP, and now I need create one more table (EMP_TWO) with same schema, same columns, and same constraints. How can I do this?

To create a new table based on another tables structure / constraints use :

CREATE TABLE new_table LIKE old_table;     

To copy the data across, if required, use

INSERT INTO new_table SELECT * FROM old_table;  

Create table docs

Beware of the notes on the LIKE option :

> Use LIKE to create an empty table based on the definition of another > table, including any column attributes and indexes defined in the > original table: > > CREATE TABLE new_table LIKE original_table; The copy is created using the same > version of the table storage format as the original table. The SELECT > privilege is required on the original table. > > LIKE works only for base tables, not for views. > > CREATE TABLE ... LIKE does not preserve any DATA DIRECTORY or INDEX > DIRECTORY table options that were specified for the original table, or > any foreign key definitions.

If you want to copy only Structure then use

create table new_tbl like old_tbl;

If you want to copy Structure as well as data then use

create table new_tbl select * from old_tbl;

Create table in MySQL that matches another table? Ans:

CREATE TABLE new_table AS SELECT * FROM old_table;

Why don't you go like this

CREATE TABLE new_table LIKE Select * from Old_Table;   

or You can go by filtering data like this

CREATE TABLE new_table LIKE Select column1, column2, column3 from Old_Table where column1 = Value1;   

For having Same constraint in your new table first you will have to create schema then you should go for data for schema creation

CREATE TABLE new_table LIKE Some_other_Table;

by only using the following command on MySQL command line 8.0 the following ERROR is displayed
[ mysql> select * into at from af;] >ERROR 1327 (42000): Undeclared variable: at

so just to copy the exact schema without the data in it you can use the create table with like statement as follows: >create table EMP_TWO like EMP;

and to copy table along with the data use: >create table EMP_TWO select * from EMP;

to only copy tables data after creating an empty table: > insert into EMP_TWO select * from EMP;