Creating a table by copying another table's structure

**Allan Paul** · 05-31-2006

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

**pat.mclean** · 06-01-2006

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

Patrick.

**Allan Paul** · 06-01-2006

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** · 06-16-2006

U can also use this command:

CREATE TABLE [TableName] LIKE [SourceTableName]

**Chandra Veenapoosa** · 06-18-2006

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

**NamrataSV** · 06-19-2006

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

**jamesravid** · 06-19-2006

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** · 06-19-2006

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** · 11-28-2006

Originally Posted by NamrataSV

U can also use this command:

CREATE TABLE [TableName] LIKE [SourceTableName]

its wrong ...

**admin** · 11-28-2006

Originally Posted by rakeshkamble

its wrong ...

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

**Innila** · 12-04-2006

Originally Posted by Beena

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** · 04-24-2007

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

It is copy only structure of old table.

**debasisdas** · 07-12-2007

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** · 03-26-2009

Originally Posted by 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?

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

**nehrumosuru** · 03-28-2009

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** · 04-02-2009

Try following query

create table destination_table_name as (select * from source_table_name);

eg:

create table new_emp as (select * from emp);

**zamary** · 04-15-2009

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** · 04-15-2009

@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** · 04-15-2009

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** · 04-17-2009

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

Thread: Creating a table by copying another table's structure

Thread Tools

Display

Creating a table by copying another table's structure

Re: Creating a table by copying another table's structure

Re: Creating a table by copying another table's structure

Re: Creating a table by copying another table's structure

Re: Creating a table by copying another table's structure

Re: Creating a table by copying another table's structure

Re: Creating a table by copying another table's structure

Re: Creating a table by copying another table's structure

Re: Creating a table by copying another table's structure

Re: Creating a table by copying another table's structure

Re: Creating a table by copying another table's structure

Re: Creating a table by copying another table's structure

Re: Creating a table by copying another table's structure

Re: Creating a table by copying another table's structure

Re: Creating a table by copying another table's structure

Re: Creating a table by copying another table's structure

Re: Creating a table by copying another table's structure

Re: Creating a table by copying another table's structure

Re: Creating a table by copying another table's structure

Re: Creating a table by copying another table's structure

Posting Permissions

About us

GeekInterview

Interact