Oracle: How to Create a Copy of Table Data

Knowing how to copy existing table data is beneficial to any DBA. This tutorial will demonstrate how to copy an existing table’s data into a new table. Examples with walkthrough explanation are provided.

Let’s see the syntax and example for creating a copy of old_table into new_table in oracle.

Syntax:

Create table new_table
as select * from old_table;

First create a table ‘dept_info’ and insert some records into it.
create table dept_info(
dept_id number(10),
dept_name varchar(25),
constraint pk_dept_id primary key(dept_id)
);

insert into dept_info values(1,'Account');
insert into dept_info values(2,'HR');
insert into dept_info values(3,'Payroll');


see the inserted records into ‘dept_info’ table.

select * from dept_info;

   DEPT_ID DEPT_NAME
---------- -------------------------
         1 Account
         2 HR
         3 Payroll

Now copy the existing table 'dept_info' into new table called 'dept_info_bck'.

create table dept_info_bck as select * from dept_info;

Now if you see the 'dept_info_bck' table's data, all the records from 'dept_info' table are copied into 'dept_info_bck' table.

select * from dept_info_bck;

   DEPT_ID DEPT_NAME
---------- -------------------------
         1 Account
         2 HR
         3 Payroll