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

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

    CREATE 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.

  2. Truncate the original table customer_t.

    TRUNCATE customer_t;
    
  3. 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);
    
  4. Delete the temporary table customer_t_temp.

    DROP TABLE customer_t_temp;
    
Feedback
编组 3备份
    openGauss 2024-05-30 00:42:45
    cancel