Copy a table structure in MySQL

We can clone or copy a table structure in MySQL using LIKE or AS operators.

Using LIKE:

We can use CREATE TABLE … LIKE to create an empty table based on the definition of another table, including any column fields and indexes defined in the original table:

Syntax:

CREATE TABLE new_table LIKE original_table;

Example:
Create copy of EMP table with the new name EMP_NEW

CREATE TABLE EMP_NEW LIKE EMP;

Using AS: query_expression

To create one table from another, add a SELECT statement at the end of the CREATE TABLE statement:

Syntax:

CREATE TABLE new_table AS SELECT * FROM original_table;

Example:
Create copy of EMP table with the new name EMP_NEW1. This creates table and copies all rows to new table.

CREATE TABLE EM_NEW1P AS SELECT * FROM EMP;

Related Posts

Responses are currently closed, but you can trackback from your own site.

Comments are closed.

Powered by k2schools