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

User · Answer

Apart from the solution above  you can use AS to make it in one line   CREATE TABLE tbl new AS SELECT   FROM tbl old

User · Answer

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

User · Answer

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

User · Answer

I found the same situation and the approach which I took was as follows    Execute SHOW CREATE TABLE  lt table name to clone gt    This will give you the Create Table syntax for the table which you want to clone 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

User · Answer

To copy with indexes and triggers do these 2 queries   CREATE TABLE newtable LIKE oldtable   INSERT INTO newtable SELECT   FROM oldtable    To copy just structure and data use this one   CREATE TABLE tbl new AS SELECT   FROM tbl old    I ve asked this before   Copy a MySQL table including indexes

User · Answer

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  gt  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

User · Answer

MySQL way   CREATE TABLE recipes new LIKE production recipes  INSERT recipes new SELECT   FROM production recipes

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] Duplicating a MySQL table, indices, and data

Examples related to mysql