How To Create Table In Plsql

Create a duplicate table
in Oracle database

When working with an Oracle database, you may need to create a copy of the table to test or develop your application properly. We have selected the best ways to accomplish different types of table copying in Oracle and provided an accurate description of each method.

In this article, you will learn how to perform the following:

Create an exact copy of the table within the same schema
Copy only the table structure without copying data
Insert duplicate data into an already existing table
Copy a table between different database schemas
Use the GUI tool to implement any type of table copying

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

Create a duplicate table
with dbForge Studio for Oracle

Running queries manually requires significant time spending, that's why if you strive to be efficient, you will probably want to use an Oracle IDE that will help you achieve your goals within a convenient graphical user interface and boost your flexibility with an opportunity to adjust the process to your requirements.

Step 1. Open dbForge Studio for Oracle

First of all, you should start dbForge Studio for Oracle and connect to your database, just as shown on the screenshot.

Choose a connection type and enter proper credentials to your Oracle database instance. Test the connection if necessary.

Database connection properties

Step 2. Create a new table

In the Database Explorer, click on the connection node and find the Tables folder. Right-click the Tables folder and select New Table.

Click New Table

Step 3. Create a table structure

In the Table Editor that opens, specify the name for the new table and build the structure of the table.

You need to make sure the table columns and column properties are the same as the columns of the existing table.

Click Apply Changes and check that you have created a new table in the Database Explorer.

Table editor window

Step 4. Open the Data Comparison Wizard

Now, you can fill the table with data. To do that, go to the Comparison tab on the menu and open the Data Comparison Wizard. Specify the connection and select the schema.

Configure the rest of the comparison options if required.

Source and target for data comparison

Step 5. Configure data comparison options

If you want to copy the data to a table within the same Oracle schema, go to the Mapping page and select the tables for mapping. Here, you can map tables with different names and structures.

Click the Map Objects button on the toolbar, and the Objects Mapping dialog will appear. There, you need to specify the source and target tables and click Map. The objects will then appear in the grid of the Mapping tab. You can skip the table mapping if you want to compare the data between different schemas. Now, click Compare to start the comparison.

Objects Mapping tab in dbForge Studio for Oracle

Step 6. Analyze the comparison results

View the comparison results in the Data Comparison document. Here, you can select the necessary objects for synchronization and initiate the synchronization by clicking the Green Arrow.

Comparison results showing differences

Step 7. Define synchronization options

In the Data Synchronization Wizard that opens, you can decide on your further actions as to the synchronization script.

You can open the script in the internal editor, save it for later use, or execute it directly against the target.

Output options for the sync script

Step 8. Continue configuring the data sync

After that, you can configure the data synchronization options as well as check the action plan and the list of warnings on the Summary tab. Click Synchronize to start synchronizing the data.

Summary tab in Data Synchronization Wizard

Step 9. Make sure the tables are equal

Perform the data comparison one more time to ensure the tables are identical. Congratulations! You have cloned an Oracle table!

Data comparison document shows the tables are equal

Conclusion

In this article, we have reviewed various ways of creating a duplicate table in Oracle: we have started by executing Oracle SQL commands and then looked at the way the same procedures are performed via an intuitive interface of dbForge Studio for Oracle. The IDE delivers robust comparison functionality and empowers you to take your Oracle database interaction to a whole new level. Check out other useful features provided by this powerful Oracle GUI tool!