How to Create an Empty Table by CTAS

  • by

Empty Table

To duplicate a table, the fast way is to use CTAS (Create Table As Select). Sometimes, the only thing you want is an empty table with the same definition as the original one.

In this case, I would like to create an empty table from an existing table with rows over 1 billion. What I did is to make every row pass through our filter.

SQL> set timing on
SQL> create table ERP.PAY_HIST_BAK as select * from ERP.PAY_HIST where 1 = 2;

Table created.

Elapsed: 00:00:00.29

As you can see, I created the empty table in less than 1 second. The trick is to use where 1 = 2 to make every row escape from the condition.

Loading Data

Later on, if you want all data to be loaded into the new table, you can just use this:

SQL> insert into ERP.PAY_HIST_BAK select * from ERP.PAY_HIST;

The strategy is pretty flexible for DBA to manipulate data from one to another.

Leave a Reply

Your email address will not be published. Required fields are marked *