MySQL – Clone Tables

MySQL – Clone Tables

There may be a situation when you need an exact copy of a table and CREATE TABLE ... SELECT doesn't suit your purposes because the copy must include the same indexes, default values and so forth.
You can handle this situation by following the steps given below −

  • Use SHOW CREATE TABLE to get a CREATE TABLE statement that specifies the source table's structure, indexes and all.
  • Modify the statement to change the table name to that of the clone table and execute the statement. This way, you will have the exact clone table.
  • Optionally, if you need the table contents copied as well, issue an INSERT INTO ... SELECT statement, too.
    Use SHOW CREATE TABLE to get a CREATE TABLE statement that specifies the source table's structure, indexes and all.
    Modify the statement to change the table name to that of the clone table and execute the statement. This way, you will have the exact clone table.
    Optionally, if you need the table contents copied as well, issue an INSERT INTO ... SELECT statement, too.

    Example

    Try out the following example to create a clone table for tutorials_tbl.
    Step 1 − Get the complete structure about the table.

    mysql> show create table products_tableG;
    *************************** 1. row ***************************
    Table: products_table
    Create Table: CREATE TABLE `products_table` (
    `supplier_ids` longtext,
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `product_code` varchar(30),
    `prd_name` varchar(30) NOT NULL DEFAULT 'demo',
    `description` longtext,
    `standard_cost` decimal(19,4) DEFAULT '0.0000',
    `list_price` decimal(19,4) NOT NULL DEFAULT '0.0000',
    `reorder_level` int(11) DEFAULT NULL,
    `target_level` int(11) DEFAULT NULL,
    `attachments` longblob,
    `quantity_per_unit` varchar(50) DEFAULT NULL,
    `discontinued` tinyint(1) NOT NULL DEFAULT '0',
    `minimum_reorder_quantity` int(11) DEFAULT NULL,
    `category` varchar(50) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `product_index` (`id`),
    UNIQUE KEY `product_indexes` (`id`),
    KEY `product_code` (`product_code`),
    KEY `category` (`category`),
    KEY `prd_name` (`prd_name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)
    ERROR: No query specified

    Step 2 − Rename this table and create another table.

    mysql> CREATE TABLE .`products_clone_tbl` (
    ->   `supplier_ids` LONGTEXT NULL DEFAULT NULL,
    ->   `id` INT(11) NOT NULL AUTO_INCREMENT,
    ->   `product_code` VARCHAR(25) NULL DEFAULT NULL,
    ->   `product_name` VARCHAR(50) NULL DEFAULT NULL,
    ->   `description` LONGTEXT NULL DEFAULT NULL,
    ->   `standard_cost` DECIMAL(19,4) NULL DEFAULT '0.0000',
    ->   `list_price` DECIMAL(19,4) NOT NULL DEFAULT '0.0000',
    ->   `reorder_level` INT(11) NULL DEFAULT NULL,
    ->   `target_level` INT(11) NULL DEFAULT NULL,
    ->   `quantity_per_unit` VARCHAR(50) NULL DEFAULT NULL,
    ->   `discontinued` TINYINT(1) NOT NULL DEFAULT '0',
    ->   `minimum_reorder_quantity` INT(11) NULL DEFAULT NULL,
    ->   `category` VARCHAR(50) NULL DEFAULT NULL,
    ->   `attachments` LONGBLOB NULL DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   INDEX `product_code` (`product_code` ASC))
    -> ;
    Query OK, 0 rows affected (0.24 sec)

    Step 3 − After executing step 2, you will create a clone table in your database. If you want to copy data from old table then you can do it by using INSERT INTO... SELECT statement.

    mysql> INSERT INTO `products_clone_tbl` (`supplier_ids`, `id`, `product_code`, `prd_name`, `description`, `standard_cost`, `list_price`, `reorder_level`, `target_level`, `quantity_per_unit`, `discontinued`, `minimum_reorder_quantity`, `category`, `attachments`)
    -> SELECT `supplier_ids`, `id`, `product_code`, `prd_name`, `description`, `standard_cost`, `list_price`, `reorder_level`, `target_level`, `quantity_per_unit`, `discontinued`, `minimum_reorder_quantity`, `category`, `attachments` FROM `products_table`;
    Query OK, 3 rows affected (0.06 sec)
    Records: 3  Duplicates: 0  Warnings: 0

    Finally, you will have an exact clone table as you wanted to have.

MySQL – ALTER Command (Prev Lesson)
(Next Lesson) MySQL – Database Info
', { 'anonymize_ip': true });