[SQL-Oracle] Copying data from one table to another

Posted by cikul | Posted in SQL | Posted on 29-06-2012-05-2008


How to simply copy data from one Oracle table into another one? There is several ways to copy data, the common usage is exporting the data and then reupload the data to new table.

The fastest way to copy data from another table is using statement :

CREATE Table new_table AS
SELECT * FROM old_table

above statement will creating new table that are identical (structure and data) with old table, and this is the fastest way.

But if you already has the destination table and won’t to re-create the table, you can use this statement :

 INSERT INTO new_table
 SELECT * FROM old_table

above statement is working if you have similar fields with old tables, if you only want copy selected fields, you must change statement in 2nd line.

For speed/performance, you can check this link.

Copy Table Structure

but how if you only want to copy table structure, without the data? you can try this statement :

SELECT * FROM old_table WHERE 1=0;

Share and Enjoy

Write a comment