CODEWITHSUNDEEP
mysql
user1016403
user1016403

Reputation: 12621

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?

Upvotes: 101

Views: 101448

Answers (6)

happysailingdude
happysailingdude

Reputation: 305

SHOW CREATE TABLE tablename

gives you the sql to recreate your table including indexes etc - just change the name to your desired new table name :)

Upvotes: 0

Rajan
Rajan

Reputation: 1

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;

Upvotes: 0

lipak
lipak

Reputation: 101

Create table in MySQL that matches another table? Ans:

CREATE TABLE new_table AS SELECT * FROM old_table;

Upvotes: 10

Karan
Karan

Reputation: 587

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;

Upvotes: 24

Mohit
Mohit

Reputation: 11314

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;

Upvotes: 4

Manse
Manse

Reputation: 38147

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.

Upvotes: 208

Related Questions