# Databases > Oracle Creating a table by copying another table's structure

## Allan Paul

How could I create a new table by copying another table's structure?

----------


## pat.mclean

create table target tablename as (select * from source tablename);

Patrick. :Cool:

----------


## Allan Paul

Thanks for the reply.

So for example, the name of the first table is *ABC*, then the new table is *DEF*. The syntax would be:

create table target DEF as (select * from ABC);

Am I correct?

----------


## NamrataSV

U can also use this command:

CREATE TABLE [TableName] LIKE [SourceTableName]

----------


## Chandra Veenapoosa

is it the same in Oracle and DB2... could some one explain in relats to different databases available...

----------


## NamrataSV

This commands works for DB2...not sure about Oracle

----------


## jamesravid

Create table [tablename] like [sourcetablename] 

The above command does not work in oracle. But the following commands works in oracle 

create table DEF as (select * from abc); 

But an important thing is the above command copies the structure along with the data in table abc. That is, the above create table command creates a duplicate/backup table of source table. 

* sql> create table abc (id number,name varchar2(20)); 
table created. 

Sql> desc abc 
name null? type
 ----------------------------------------- -------- ---------------------------- 
id number
name varchar2(20) 

sql> insert into abc values (1, 'user1');
1 row created. 

Sql> insert into abc values (2,'user2');
1 row created. 
Sql> select * from abc;

 id name 
---------- --------------------
 1 user1
 2 user2 

sql> create table def as (select * from abc);
 table created. 
Sql> desc def 
name null? type 
----------------------------------------- -------- ----------------------------
 id number 
 name varchar2(20) 

Sql> select * from def; 
id name 
---------- -------------------- 
1 user1 
2 user2* 

In above sqls, we created abc table and inserted two records in it. Then we created def table by copying the structure of abc table. But this command also copies the data along with the structure. If your requirement is only to copy the structure then you need to follow a trick to filter records in the source table. The following is a simple trick to filter the records 

*
sql> create table def as (select * from abc where 1=2); 
table created. Sql> desc def name null? type ----------------------------------------- -------- ---------------------------- id number name varchar2(20) sql> select * from def; no rows selected* we have added one where condition (1=2) in the inner select statement. It is obvious that 1 is not equal to 2 and this condition is applied on each column of the source table. Since 1 is not equal to 2 always all records in the source table are filtered. So the create table command that we have introduced later copies only the structure of the source table.

----------


## jamesravid

*Create table [tablename] like [sourcetablename]* 

the above command does not work in oracle. But the following commands works in oracle 

*create table def as (select * from abc);* 

but an important thing is the above command copies the structure along with the data in table abc. That is, the above create table command creates a duplicate/backup table of source table. 

* 
sql> create table abc (id number,name varchar2(20));
 table created. 

Sql> desc abc 
name null? type
----------------------------------------- -------- ---------------------------- 
id number 
name varchar2(20) 

sql> insert into abc values (1, 'user1');
 1 row created.

 Sql> insert into abc values (2,'user2');
 1 row created.

Sql> select * from abc;
id name ---------- --------------------
 1 user1
 2 user2 

sql> create table def as (select * from abc);
 table created. 

Sql> desc def 
name null? type 
----------------------------------------- -------- ---------------------------- 
id number 
name varchar2(20) 

sql> select * from def; 
id name 
---------- -------------------- 
1 user1 
2 user2* 

In the above sqls, we created abc table and inserted two records in it. Then we created def table by copying the structure of abc table. But this command also copies the data along with the structure. If your requirement is only to copy the structure then you need to follow a trick to filter records in the source table. The following is a simple trick to filter the records, 

*
sql> create table def as (select * from abc where 1=2);
 table created. 

Sql> desc def 
name null? type 
----------------------------------------- -------- ---------------------------- 
id number 
name varchar2(20) 

sql> select * from def; 
no rows selected* 

We have added one where condition (1=2) in the inner select statement. It is obvious that 1 is not equal to 2 and this condition is applied on each column of the source table. Since 1 is not equal to 2 always all records in the source table are filtered. So the create table command that we have introduced later copies only the structure of the source table.

Thanks,
James

----------


## rakeshkamble

> U can also use this command:
> 
> CREATE TABLE [TableName] LIKE [SourceTableName]


its wrong ...

----------


## admin

> its wrong ...


if you know the answer, please reply instead of just saying it's wrong...

----------


## Innila

> is it the same in Oracle and DB2... could some one explain in relats to different databases available...


In Oracle database, a table can be created with another table's structure by using,
CREATE TABLE emp_new AS SELECT * FROM emp;
This will create the table with the records existing in emp table. 
To avoid the records, the following statement can be used, 
CREATE TABLE emp_new AS SELECT * FROM emp WHERE 1=2;

In SQL Server 2000, a table can be created using another table, similar to create table statement of Oracle. 
There is another statement to create a table using SELECT statement.
SELECT * INTO emp_new from emp where 1=2
This will create the table structure alone.

----------


## yuvarajan2000

create table new_Table_name as (select * from old_table_name where 1=2);

It is copy only structure of old table.

----------


## debasisdas

create table *new_table* as select * from *existing_table*;

the above code will copy both data and structure

create table *new_table* as select * from *existing_table* where 1=2;

the above code will copy only structure without data.

NOTE--instead of using 1=2 ,you can use any statment that evalutes to false.

----------


## aparna.kumbhar

> Thanks for the reply.
> 
> So for example, the name of the first table is *ABC*, then the new table is *DEF*. The syntax would be:
> 
> create table target DEF as (select * from ABC);
> 
> Am I correct?


it should be...
create table DEF as (select * from ABC);

----------


## nehrumosuru

Hi 

Try this following query

create table  as select *from 
where  is null 

the above query copied only data structure from Data table name

----------


## phanikanthmn

Try following query

create table destination_table_name as (select * from source_table_name);

eg:

create table new_emp as (select * from emp);

----------


## zamary

create table emp1 as select * from emp where 1=2

but my question how about the constraints , the above query not copied the constrains
that are assigned to the emp table

zamary shahab
afghanistan , jalalabad city

----------


## ecearund

@zamary,

Create table emp1 as select * from emp where 1=2
See, here u are trying to create a new table "emp1" with same structure of what the "emp" table has with no data.

But, as you said  the above query will never copy the constraints of what "emp" table has.

If u actually want to copy the data+structure+constraints, then u need to take backup of emp table and import it.
Either by export/import.
expdp/impdp-Oracle Database 10g Data Pump.

----------


## parag_tyagi2001

hi,
there are 2 ways : 

first is : if u want to create a table by copying only structure of another table not data then use : 
create table  as (select  * from  where 1=2)

second is : if u want to create table with data and structure then use  : 
create table  as (select  * from )

----------


## kingrana

Create table newtablename as ( select * from oldtablename);
this query will create a new table with same structure as the old one as well as all the records will be  copied in the new table

----------

