MySQL Clone Table

MySQL does not have a clone command. To Clone a table is to create a replica, and for creating a coy of the table, we can use:

1. Create a table using the select statement
2. Create a table and then insert it with the select clause

Pre-requisites

Command-line client:

• Log in to the command-line client with user credentials. The User should have select, Create and Insert table privileges.
• Select a database to work with using the USE statement.

MySQL Workbench:

• Open a new connection with user credentials. The User should have select, create, and insert table privileges.
• Select a database with the USE statement.

Create table...Select in MySQL using the command line client

• To make a copy of a table as-is, we do:

CREATE TABLE <new_table-name> 

SELECT * from <orig_table_name>;

example:

CREATE TABLE employees2
SELECT * FROM employees;

• The command above would create a new table and insert all the data as well. It does not copy triggers, indexes, etc., from the earlier table to the next one.

CREATE Table ... Select specific columns in MySQL using command-line client

• Instead of creating a new table with the same columns, we can choose the columns we require and even combine columns as in the below example.

  create table employee_subset
  select emp_id, CONCAT(first_name," , ",last_name)
  from employees;

• All the records based on the columns we have selected are inserted into the new table.

Copy a table from another database in MySQL using MySQL Workbench

• The syntax to copy a table from one database to the other is as follows:

CREATE table <current_schema.table_name> 
select * from <other_Schema.table_name>;

Example: To copy the city table from the Sakila database to the current database.

 CREATE TABLE TESTDB.CITY SELECT * FROM sakila.CITY;

Create a table and insert records in MySQL using MySQL Workbench

• To create a table with the same structure as some table, we use:

Create Table <table_name> Like <other_table>

e.g., To create a table like the country table of the Sakila database in our current database, we do 

CREATE TABLE TEST_TABLE LIKE sakila.COUNTRY;

• The above command copies the table structure but not the data.

• After creating the table, we can either Insert data from the other table or entirely new values. 
• To insert data from the other table, we do

INSERT INTO TEST_TABLE
select * from sakila.country;

Select * from TEST_TABLE;