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
 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.