How To Copy Table Data To Another In Oracle

How To Copy Table Data To Another In Oracle | In this post, we will learn how to copy table data to another in Oracle. The new table may contain only a few columns & row from another table based on some condition or it will be a duplicate of the existing table.

Create Duplicate Table In Oracle

Here we will create an exact duplicate table of an existing table with the same structure and data. We have emp & dept tables in our Oracle database we will use the employee table to create a duplicate table in Oracle.

CREATE TABLE employees AS
(SELECT * FROM emp);

We have created a duplicate table of the emp table. You can check their structure and data using the below queries.

DESC emp;
DESC employees;
SELECT * FROM emp;
SELECT * FROM employees;

Limitations:- Whenever we create a duplicate table using the above query then it copies structure and data but doesn’t copy constraint and other objects. We have to explicitly add the constraint to the new table. Let us understand it through an example:-

CREATE TABLE uniquetest (
  sno NUMBER(10) UNIQUE,
  name VARCHAR2(10)
);
INSERT INTO uniquetest VALUES(10, 'A');
INSERT INTO uniquetest VALUES(20, 'B');
DESC uniquetest;
SELECT * FROM uniquetest;
INSERT INTO uniquetest VALUES(20, 'C');

We have created a “uniquetest” table and its “sno” column contains UNIQUE constraint therefore this column values always will be unique and won’t contain duplicate data. If we try to insert a duplicate entry for the “sno” column then we will get an error:- ORA-00001: unique constraint (KP.SYS_C007267) violated. Now, let us create a duplicate table from it, and try to insert a duplicate entry for the “sno” column.

CREATE TABLE u_1
AS
SELECT * FROM uniquetest;

DESC u_1;
SELECT * FROM u_1;

INSERT INTO u_1 VALUES(20, 'C');
SELECT * FROM u_1;

This time we have successfully inserted a duplicate entry for the “sno” column with the value 20. If we want to apply the UNIQUE constraint to the “sno” column of the u_1 table similar to the “uniquetest” table then we have to enable the UNIQUE constraint using ALTER … ADD command. See more:- UNIQUE Constraint

Create Duplicate Table Without Data In Oracle

Sometimes we want to copy only the structure of the table but not its data, in that case, we have to create a duplicate table without data.

CREATE TABLE emp_1 AS
(SELECT * FROM emp WHERE 1 = 0); 

See the structure and the data of the table. Currently, it doesn’t contain any data.

DESC emp_1;
SELECT * FROM emp_1;

Limitation:- Similar to the above example it also won’t contain the constraint information on the table. We have to explicitly add the constraint on the table.

Oracle Copy Data From One Table To Another

This query is used to create the table without data. If you need the data to be inserted then Let’s insert the data to the new table i.e. copy the data of the above table to the new table.

INSERT INTO emp_1 (SELECT * FROM emp);

We can also copy data from one table to another based on some conditions. For example, we want to insert only those rows where “comm” is NOT NULL in the emp table.

INSERT INTO emp_1
(SELECT * FROM emp WHERE comm IS NOT NULL);

If we want to copy data from only a particular column and based on some conditions then you can write similar to the below query:-

INSERT INTO emp_1(empno, ename)
(SELECT empno, ename FROM emp WHERE sal > 2500);

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Leave a Comment

Your email address will not be published. Required fields are marked *