How to transfer all data from one table to another table in Oracle?-CodePudding

How to transfer data from one table to another in oracle? All fields in both tables are identical.

insert into second_table (column_1, column_2, column_3) 
values
select column_1, column_2, column_3
from first_table;
commit;

The above method will only write the data to the second table, but will not delete the data from the first table.

Is there any function in oracle library that can solve this problem?

CodePudding user response：

As you can't do that in just one command, you'll have to use two: insert delete.

Note that syntax you posted is invalid; you can't have values keyword in this context.

Therefore:

insert into second_table (column_1, column_2, column_3) 
select column_1, column_2, column_3
  from first_table;

truncate table first_table;

As truncate is considered to be a DDL, it implicitly commits so - you don't have to explicitly commit.

However, note that truncate (or delete) might fail if there are foreign key constraints which would prevent those rows to be removed from the first_table, and there's no on delete cascade specified.

CodePudding user response：

Why wouldn't you just have a view of the origin table and use the WHERE clause to 'delete' the records you don't want?

CodePudding user response：

To expand on Littlefoot response you may want to consider using /* APPEND */ for the INSERT to speed things up.

As for the constraints you can disable them if need be. Here is an example that will get you started. When done you should enable them if needed.


DECLARE 
 sql_stmt varchar2(255);
 t1 pls_integer;
BEGIN
   t1 := dbms_utility.get_time; 
FOR c IN
    (
      SELECT * 
        FROM user_constraints c
       WHERE -- c.constraint_type = 'P' AND
          c.table_name = 'HOLIDAYS')
  LOOP
    sql_stmt:='ALTER TABLE '||c.table_name||
                      ' DISABLE CONSTRAINT '||c.constraint_name;
   dbms_output.put_line(sql_stmt);
   EXECUTE IMMEDIATE sql_stmt;
  END LOOP;

dbms_output.put_line((dbms_utility.get_time - t1)/100 || ' seconds');

END;