Help Center/ GaussDB/ Developer Guide(Centralized_2.x)/ Importing Data/ Deep Copy/ Performing a Deep Copy by Creating a Temporary Table and Truncating the Original Table
Updated on 2023-10-23 GMT+08:00
View PDF
Share

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

Run the CREATE TEMP 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

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

    1
    		 
    openGauss=# CREATE TEMP TABLE customer_t_temp AS SELECT * FROM customer_t;
    • 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.
    • Temporary tables and common tables are stored in the same location. You can also specify a tablespace to store temporary tables. If too many local temporary tables are used, the system catalog may bloat, but the overall impact is acceptable.

  2. Truncate the original table customer_t. 

    1
    		 
    openGauss=# TRUNCATE customer_t;

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

    1
    		 
    openGauss=# INSERT INTO customer_t (SELECT * FROM customer_t_temp);

  4. Delete the temporary table customer_t_temp. 

    1
    		 
    openGauss=# DROP TABLE customer_t_temp;

Parent topic: Deep Copy