Copy Table With or Without Data Using Create Table As statement.

By Manish Sharma

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.


 CREATE TABLE  table_name_1
 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.


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

Suggested Tutorial: How to connect with the HR user of your 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
 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
 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
 SELECT first_name, last_name, email FROM employees WHERE 1=0;

These are a few handy tips and tricks hope you enjoyed reading. If so then please support us by sharing this blog with your friends on your social or by any means you find convenient. If you have any questions feel free to follow me and tweet me @RebellionRider. Thanks & have a great day!

SQL Script and Presentation used

You can DOWNLOAD SQL script and presentation used in the Video and in this article.

No SQL Script used in Video and in this article

I have used copy cloud to share these resourses. You can also join copy cloud and get 15GB free cloud storage for lifetime. If you will use this referal link You will get 5GB extra free cloud storage means total 20GB free cloud storage.

  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn
  •          View Manish Sharma's profile on LinkedIn