Performing a Deep Copy

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, including the primary key and foreign key.

Perform the following steps to perform a deep copy for the customer_t table:

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

   1 2 3 4 5 6

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

2. Run the INSERT INTO...SELECT statement to batch insert data of the original table into the copy.

   1	INSERT INTO customer_t_copy (SELECT * FROM customer_t);

3. Delete the original table.

   1	DROP TABLE customer_t;

4. Run the ALTER TABLE statement to rename the copy to the name of the original table.

   1	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 and foreign key of the original table. You can use the ALTER TABLE statement to add them.

Procedure

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

   1	CREATE TABLE customer_t_copy (LIKE customer_t);

2. Run the INSERT INTO...SELECT statement to batch insert data of the original table into the copy.

   1	INSERT INTO customer_t_copy (SELECT * FROM customer_t);

3. Delete the original table.

   1	DROP TABLE customer_t;

4. Run the ALTER TABLE statement to rename the copy to the name of the original table.

   1	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 and foreign key of the original table. This method is recommended if the original table has dependency items.

Compared with the use of permanent tables, the use of temporary tables can improve performance but may incur data loss. Temporary tables appear only during your current session and delete themselves when you close it. To keep your data safe, choose a permanent table instead.

Procedure

1. Run the CREATE TABLE AS statement to create a temporary table customer_t_temp for the customer_t table.

   1	CREATE TABLE customer_t_temp AS SELECT * FROM customer_t;

2. Truncate the original table customer_t.

   1	TRUNCATE customer_t;

3. Run the INSERT INTO...SELECT statement to batch insert data of the temporary table into the original table.

   1	INSERT INTO customer_t (SELECT * FROM customer_t_temp);

4. Delete the temporary table customer_t_temp.

   1	DROP TABLE customer_t_temp;