How to copy data from one table to another on Oracle?

Last Updated on :July 26, 2020

Oracle database has syntax “CREATE TABLE … AS SELECT … “which allows you copy data from one table to another without predefining the target table.

If you want to create a copy of source table with data. You can write a simple query like below

CREATE  TABLE target_table
As
SELECT  *
FROM source_table;

If you want to create a copy of source table without copying the data then you can just add where clause that will not select any data.

CREATE  TABLE target_table
As
SELECT  *
FROM source_table WHERE 1=2;

Note: This will not copy Triggers, indexes, Constraints etc. from source table to target table

If target table is already defined then you can copy the data using “INSERT into ……SELECT * FROM…..” You can refer the below query

insert into  target_table select * from source_table;

Above query will execute if both the tables have identical structures (same number of columns, each matching pair of columns with same datatypes)

If both the tables does not have same structure then you must provide the name of the columns you are inserting to otherwise, you will get SQL Error.

--Creating Person Table

CREATE TABLE PERSON
(
PERSON_ID     NUMBER NOT NULL ,
FIRSTNAME     VARCHAR2(20) NULL ,
LASTNAME      VARCHAR2(30) NULL ,
INACTIVE_DATE DATE NULL ,
CONSTRAINT PK_PERSON PRIMARY KEY (PERSON_ID)
);

--Creating EMPLOYEES Table

CREATE TABLE  EMPLOYEES
(
EMPLOYEE_ID  NUMBER NOT NULL,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
EMAIL VARCHAR2(25),
PHONE_NUMBER VARCHAR2(20),
HIRE_DATE DATE NOT NULL,
JOB_ID VARCHAR2(10) NOT NULL,
SALARY NUMBER,
COMMISSION_PCT  NUMBER,
MANAGER_ID  NUMBER,
DEPARTMENT_ID NUMBER,
CONSTRAINT PK_EMPLOYEES PRIMARY KEY (EMPLOYEE_ID)
);

--Inserting data from EMPLOYEES table to PERSON table

INSERT INTO PERSON
SELECT * FROM EMPLOYEES;

Output
SQL Error: ORA-00913: too many values
In below query I am providing column name to copy data from EMPLOYEES table to PERSON table

INSERT
INTO PERSON
(
PERSON_ID,
FIRSTNAME,
LASTNAME,
INACTIVE_DATE
)
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
NULL INACTIVE_DATE
FROM EMPLOYEES;

Output
109 rows inserted.