Copy a table with data within the same Oracle database
1. Oracle provides convenient syntax that helps implement the above-mentioned scenarios.
By means of the “CREATE TABLE … AS SELECT … ” command, you can create a duplicate table
within the same database schema. To create an exact copy of the table preserving the table
structure and all the data, execute the query as follows:
CREATE TABLE new_table_name
AS
SELECT *
FROM existing_table_name;
2. If you want to limit your data copying to specific columns, indicate the column names
after SELECT in the following way:
CREATE TABLE new_table_name
AS
SELECT column_name1,column_name2
FROM existing_table_name;
3. Provided that you have already created a table, and you want to insert the data from the
existing table into it, run the following:
INSERT INTO new_table_name
SELECT *
FROM existing_table_name;
Copy a table without data within the same Oracle database
4. In some cases, you may want to copy the table structure but leave out the data. Similarly,
you can use the “CREATE TABLE … AS SELECT … ” command, but this time you need to modify it
by adding the WHERE clause that is false and, consequently, will not select any data, for instance:
CREATE TABLE new_table_name
AS
SELECT *
FROM existing_table_name WHERE 1=5;
Copy a table from one Oracle database to another
5. To copy a table from one database schema to another one, you need to apply an SQL*Plus
COPY command. This powerful command allows you to actually copy data between different
servers. However, we will focus on copying a table between different database schemas in Oracle.
The basic syntax for the command looks as follows:
COPY FROM source_database TO target_database action -
destination_table (column_name, column_name, -
column_name ...) USING query
Let’s define the important prerequisites for using this command:
-
You need to have the access to the specified tables
and know the valid credentials for the local and remote databases in the FROM and/or TO clause.
-
You can indicate the new names for the columns in the destination table, otherwise, they will
have the same names by default.
- You have to choose between four actions—REPLACE, CREATE, INSERT, or
APPEND, the one that suits your particular case.
-
In the USING clause, you need to write a query that will be used to specify the data for copying.
Here, you are free to use any type of SELECT.
To create a new table and then copy the data, use CREATE; to fill the created
table with data, use INSERT; to replace the created table together with its contents,
use REPLACE. Besides, you can use APPEND, which works both ways: if you have created a new table,
it will fill it with data; if you haven’t, it will first create a table
and then insert the necessary data.
Let’s see an example of the COPY command
that copies three columns from the source table and copies only the rows in which the EMPLOYEE_ID
value is greater than 50:
COPY FROM EMPLOYEES@DB -
REPLACE DBCOPY1 -
USING SELECT FIRST_NAME, LAST_NAME, SALARY -
FROM DETAILS_VIEW -
WHERE EMPLOYEE_ID > 50
