Duplicating a MySQL table, indices, and data

Question

How do I copy or clone or duplicate the data, structure, and indices 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 indices:

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

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

create table {new_table} like {old_table};

Answer 1

To copy with indexes and triggers do these 2 queries:

CREATE TABLE new_table LIKE old_table; 
INSERT INTO new_table SELECT * FROM old_table;

To copy just structure and data use this one:

CREATE TABLE new_table AS SELECT * FROM old_table;

I've asked this before:

Copy a MySQL table including indexes

Answer 2

Apart from the solution above, you can use AS to make it in one line.

CREATE TABLE tbl_new AS SELECT * FROM tbl_old;

Answer 3

MySQL way:

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

Answer 4

Go to phpMyAdmin and select your original table then select "Operations" tab in the "Copy table to (database.table)" area. Select the database where you want to copy and add a name for your new table.

Answer 5

I found the same situation and the approach which I took was as follows:

1. Execute SHOW CREATE TABLE <table name to clone> : 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).

Answer 6

The better way to duplicate a table is using only DDL statement. In this way, independently from the number of records in the table, you can perform the duplication instantly.

My purpose is:

DROP TABLE IF EXISTS table_name_OLD;
CREATE TABLE table_name_NEW LIKE table_name;
RENAME TABLE table_name TO table_name_OLD;
RENAME TABLE table_name _NEW TO table_name;

This avoids the INSERT AS SELECT statement that, in case of table with a lot of records can take time to be executed.

I suggest also to create a PLSQL procedure as the following example:

DELIMITER //
CREATE PROCEDURE backup_table(tbl_name varchar(255))
BEGIN
  -- DROP TABLE IF EXISTS GLS_DEVICES_OLD;
  SET @query = concat('DROP TABLE IF EXISTS ',tbl_name,'_OLD');
  PREPARE stmt FROM @query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

  -- CREATE TABLE GLS_DEVICES_NEW LIKE GLS_DEVICES;
  SET @query = concat('CREATE TABLE ',tbl_name,'_NEW LIKE ',tbl_name);
  PREPARE stmt FROM @query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

  -- RENAME TABLE GLS_DEVICES TO GLS_DEVICES_OLD;
  SET @query = concat('RENAME TABLE ',tbl_name,' TO ',tbl_name,'_OLD');
  PREPARE stmt FROM @query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;  

  --  RENAME TABLE GLS_DEVICES_NEW TO GLS_DEVICES;
  SET @query = concat('RENAME TABLE ',tbl_name,'_NEW TO ',tbl_name);
  PREPARE stmt FROM @query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt; 
END//
DELIMITER ;

Have a nice day! Alex

Answer 7

To duplicate a table and its structure without data from a different a database use this. On the new database sql type

CREATE TABLE currentdatabase.tablename LIKE olddatabase.tablename

Answer 8

Worths to mention the importance of checking if the table already exists before trying to duplicate it:

CREATE TABLE IF NOT EXISTS new_table LIKE old_table;

INSERT new_table
SELECT * FROM old_table;

As was said by the previous answers this will copy structure, data and all the dependent objects of the table.

See MySql Tutorial:

Answer 9

Simple Cloning: it create a table from another table without taking into account any column attributes and indexes.

CREATE TABLE new_table SELECT * FROM original_table;

Shallow Cloning: This will only create an empty table base on the structure of the original table

CREATE TABLE new_table LIKE original_table;

The following command would create an empty table base on the original table.

CREATE TABLE adminUsers LIKE users;

Deep Cloning: This means the new table gets to have all the attributes of each column and indexes of the existing table. This quite useful if you want to maintain the indexes and attributes of the existing table.

CREATE TABLE new_table LIKE original_table;

INSERT INTO new_table SELECT * FROM original_table;

https://towardsdatascience.com/how-to-clone-tables-in-sql-dd29586ec89c

Answer 10

// To copy specific column data use this one:
CREATE TABLE ut_axis_existrec LIKE ut_karvy_annexure; // To create new table

INSERT INTO ut_axis_existrec
(funding_ac,micr_no, warrant_no,
amount,invname,mfundcode,funding_dt,status,remarks1,amc_remark,created_at) 
SELECT  
t1.funding_ac,
t1.micr_no,
t1.warrant_no,
t1.amount,
t1.invname,
t1.mfund_code,
t1.funding_dt,
t1.status,
t1.remarks1,
t1.created_at
from ut_axis_karvy
inner join 
ut_axis_karvy_master as t2
on t1.micr_no = t2.micr_no;

Answer 11

After I tried the solution above, I come up with my own way.

My solution a little manual and needs DBMS.

First, export the data.

Second, open the export data.

Third, replace old table name with new table name.

Fourth, change all the trigger name in the data (I use MySQL and it show error when I don't change trigger name).

Fifth, import your edited SQL data to the database.

Answer 12

Expanding on this answer one could use a stored procedure:

CALL duplicate_table('tableName');

Which will result in a duplicate table called tableName_20181022235959 If called when

SELECT NOW();

results:

2018-10-22 23:59:59

---

Implementation

DELIMITER $$
CREATE PROCEDURE duplicate_table(IN tableName VARCHAR(255))
  BEGIN
    DECLARE schemaName VARCHAR(255) DEFAULT SCHEMA();
    DECLARE today VARCHAR(14) DEFAULT REPLACE(REPLACE(REPLACE(NOW(), '-', ''), ' ', ''), ':', ''); -- update @ year 10000
    DECLARE backupTableName VARCHAR(255) DEFAULT CONCAT(tableName, '_', today);

    IF fn_table_exists(schemaName, tableName)
      THEN
        CALL statement(CONCAT('CREATE TABLE IF NOT EXISTS ', backupTableName,' LIKE ', tableName));
        CALL statement(CONCAT('INSERT INTO ', backupTableName,' SELECT * FROM ', tableName));
        CALL statement(CONCAT('CHECKSUM TABLE ', backupTableName,', ', tableName));
      ELSE
        SELECT CONCAT('ERROR: Table "', tableName, '" does not exist in the schema "', schemaName, '".') AS ErrorMessage;
      END IF;
  END $$
DELIMITER ;

---

DELIMITER $$
CREATE FUNCTION fn_table_exists(schemaName VARCHAR(255), tableName VARCHAR(255))
  RETURNS TINYINT(1)
  BEGIN
    DECLARE totalTablesCount INT DEFAULT (
      SELECT COUNT(*)
      FROM information_schema.TABLES
      WHERE (TABLE_SCHEMA COLLATE utf8_general_ci = schemaName COLLATE utf8_general_ci)
        AND (TABLE_NAME COLLATE utf8_general_ci = tableName COLLATE utf8_general_ci)
    );
    RETURN IF(
      totalTablesCount > 0,
      TRUE,
      FALSE
    );
  END $$
DELIMITER ;

---

DELIMITER $$
CREATE PROCEDURE statement(IN dynamic_statement TEXT)
  BEGIN
      SET @dynamic_statement := dynamic_statement;
      PREPARE prepared_statement FROM @dynamic_statement;
      EXECUTE prepared_statement;
      DEALLOCATE PREPARE prepared_statement;
  END $$
DELIMITER ;

Answer 13

To create table structure only use this below code :

CREATE TABLE new_table LIKE current_table; 

To copy data from table to another use this below code :

INSERT INTO new_table SELECT * FROM current_table;

Answer 14

Try this :

`CREATE TABLE new-table (id INT(11) auto_increment primary key) SELECT old-table.name, old-table.group, old-table.floor, old-table.age from old-table;`

I selected 4 columns from old-table and made a new table.

Answer 15

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.