Deep Copy

After data is imported, you can perform a deep copy to modify a partition key, change a row-store table to a column-store table, or add a partial cluster key. A deep copy re-creates a table and batch inserts data into the table.

MogDB provides three deep copy methods.

Performing a Deep Copy by Using the CREATE TABLE Statement

Run the CREATE TABLE statement to create a copy of the original table, batch insert data of the original table into the copy, and rename the copy to the name of the original table.

When creating the copy, you can specify table and column attributes, such as the primary key.

Procedure

Perform the following operations to carry out a deep copy for the customer_t table:

Run the CREATE TABLE statement to create the copy customer_t_copy of the customer_t table.

CREATE TABLE customer_t_copy
( c_customer_sk             integer,
  c_customer_id             char(5),
  c_first_name              char(6),
  c_last_name               char(8)
) ;

Run the INSERT INTO…SELECT statement to batch insert data of the original table into the copy.
```
INSERT INTO customer_t_copy (SELECT * FROM customer_t);
```
Delete the original table.
```
DROP TABLE customer_t;
```
Run the ALTER TABLE statement to rename the copy to the name of the original table.
```
ALTER TABLE customer_t_copy RENAME TO customer_t;
```

Performing a Deep Copy by Using the CREATE TABLE LIKE Statement

Run the CREATE TABLE LIKE statement to create a copy of the original table, batch insert data of the original table into the copy, and rename the copy to the name of the original table. This method does not inherit the primary key attributes of the original table. You can use the ALTER TABLE statement to add them.

Procedure

Run the CREATE TABLE LIKE statement to create the copy customer_t_copy of the customer_t table.
```
CREATE TABLE customer_t_copy (LIKE customer_t);
```
Run the INSERT INTO…SELECT statement to batch insert data of the original table into the copy.
```
INSERT INTO customer_t_copy (SELECT * FROM customer_t);
```
Delete the original table.
```
DROP TABLE customer_t;
```
Run the ALTER TABLE statement to rename the copy to the name of the original table.
```
ALTER TABLE customer_t_copy RENAME TO customer_t;
```

Performing a Deep Copy by Creating a Temporary Table and Truncating the Original Table

Run the CREATE TABLE …. AS statement to create a temporary table for the original table, truncate the original table, and batch insert data of the temporary data into the original table.

When creating the temporary table, retain the primary key attributes of the original table. This method is recommended if the original table has dependency items.

Procedure

Run the CREATE TABLE AS statement to create a temporary table customer_t_temp for the customer_t table.
```
CREATE TEMP TABLE customer_t_temp AS SELECT * FROM customer_t;
```
NOTE: Compared with the use of permanent tables, the use of temporary tables can improve performance but may incur data loss. A temporary table is automatically deleted at the end of the session where it is located. If data loss is unacceptable, use a permanent table.
Truncate the original table customer_t.
```
TRUNCATE customer_t;
```
Run the INSERT INTO…SELECT statement to batch insert data of the temporary table into the original table.
```
INSERT INTO customer_t (SELECT * FROM customer_t_temp);
```
Delete the temporary table customer_t_temp.
```
DROP TABLE customer_t_temp;
```

Issue