PL/SQL Copy Table

Copying tables in PL/SQL is a common task in database management. It involves duplicating the structure and data of an existing table into a new one. This operation can be accomplished using the CREATE TABLE AS SELECT statement, which allows for the creation of a new table based on the result set of a SELECT query.

In this article, we'll delve into the main concept of copying tables in PL/SQL, providing syntax and examples to demonstrate its implementation.

PL/SQL Copy Table

The primary method for copying a table in PL/SQL involves the CREATE TABLE AS SELECT statement. This statement creates a new table based on the result set of a SELECT query. The syntax for copying a table is as follows:

Syntax

CREATE TABLE new_table_name AS
SELECT column1, column2, ...
FROM original_table_name;

where,

• new_table_name: The name for the new table being created.
• original_table_name: The name of the existing table being copied.
• column1, column2, ...: Specifies the columns to be copied. Using * will copy all columns

Examples of Copying Tables in PL/SQL

Example 1: Copying Data

Let's consider a scenario where we have an employees table, and we want to create a copy of it named employees_copy.

-- Schema for original table
CREATE TABLE employees (
    employee_id NUMBER,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    department VARCHAR2(50),
    salary NUMBER
);

-- Insert some sample data
INSERT INTO employees VALUES (1, 'John', 'Doe', 'IT', 5000);
INSERT INTO employees VALUES (2, 'Jane', 'Smith', 'HR', 6000);
INSERT INTO employees VALUES (3, 'Alice', 'Johnson', 'Finance', 7000);

Output:

-- Copying the table
CREATE TABLE employees_copy AS
SELECT *FROM employees;

Output:

Table EMPLOYEES_COPY created.

The SQL query creates a new table named "employees_copy" by copying all data from the "employees" table. It duplicates the structure and content of the original table into the new one.

Example 2: Without Any Data

Let's now copy the table structure only, without any data, into a new table named employees_structure.

-- Copying only the table structure
CREATE TABLE employees_structure AS
SELECT *FROM employees
WHERE 1=0;

Output:

Table EMPLOYEES_STRUCTURE created.

The query creates a new table, "employees_structure," with the same structure as the "employees" table but no data. By using "WHERE 1=0," it ensures no rows are selected, resulting in an empty table with identical column definitions to the original.

Conclusion

Copying tables in PL/SQL is a straightforward process accomplished using the CREATE TABLE AS SELECT statement. By understanding the syntax and examples provided in this article, users can effectively duplicate tables within their databases, whether it involves copying both structure and data or just the structure. This functionality is invaluable in scenarios such as data backups, data migrations, and table transformations.

R

rishabhsi2vix

Follow

Improve

Article Tags :

• Databases
• PL/SQL