Copy Table With or Without Data Using Create Table As Statement

Create Table As

When writing an oracle app or sometime for testing purposes we are required to create a copy of a table. There can be several ways of accomplishing this task such as – 

You can manually see the structure of the table using DESC command and then write a create table DML statement and create the table.

You can use the Export and Import utility for creating the copy and many more.

I say, why bear the pain of doing all these hard work and waste your precious time when you have a simple and easy-to-use tool at hand.

Yep, you heard it right, oracle provides a simple way/command of creating a copy of a table either with the data or without the data. It is called “Create Table As” command.

“Create Table As” is a DDL command which lets you create an exact copy of a table of your database with or without the data

Syntax:

CREATE TABLE  table_name_1 

 AS

 SELECT [*]/column_name(s) FROM table_name_2 WHERE expression; 

Syntax is pretty simple CREATE TABLE is oracle reserved phrase followed by the user defined name of the table ‘table_name_1’ with which you want to create your new table then we have mandatory keyword ‘AS’ followed by a simple SELECT DML statement. 

Table_name_2 is the name of the table whose copy you want to make and table_name_1 is the name of your new table. 

Examples:

For the demonstration we will use the Employees table of HR sample schema of the Oracle Database. 

E.g. 1: How to copy both the structure and data of a table.

Suppose you want to make an exact copy of employees table of HR schema and by exact copy I mean copying the whole structure along with the data of employees table.

Copying the exact structure and data of the employees table can be done by writing a very simple Create table statement. That statement will be

 

CREATE TABLE employees_copy

 AS

 SELECT * FROM employees;

Successful execution of the above command will create the employees_copy which will be having the same structure and data as of the Employees table of HR schema.

E.g. 2. How to copy specific columns of a table along with their data.

Now suppose you want to copy only first_name, last_name or email columns with data of employees table in this case CREATE TABLE statement will be.

CREATE TABLE employees_copy

 AS

 SELECT first_name, last_name, email FROM employees;

Successful execution of the above command will create the table employees_copy this time with only column first_name, last_name and email and the data.

E.g. 3. How to copy only structure of the table without the data.

Sometimes you might be required to copy only the structure of the table sans the data. In this case what you can do is copy the whole table as shown in above examples and then truncate it later but this will need two operations or two different commands to execute for a single task: Such a waste of time & resources when you can accomplish the same just by writing a simple “Create Table As command”.

The Create Table command will remain the same as shown in above examples. You just need to add a where clause which will never set to be true or always false for example where clause such as ‘Cats’ = ‘Dog’ or 0=1. Let’s see how

 

CREATE TABLE employees_copy

 AS

 SELECT first_name, last_name, email FROM employees WHERE 1=0;