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;

This statement creates a new table named new_table_name with the same column names and data types as the original_table_name. The data is copied from the original table to the new table.

Examples of PL/SQL Copy Table

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:

employee_id	first_name	last_name	department	salary
1	John	Doe	IT	5000
2	Jane	Smith	HR	6000
3	Alice	Johnson	Finance	7000

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

Output:

employee_id	first_name	last_name	department	salary
1	John	Doe	IT	5000
2	Jane	Smith	HR	6000
3	Alice	Johnson	Finance	7000

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:

employee_id	first_name	last_name	department	salary

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.