Creating a table by copying another table's structure

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

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

Patrick.:cool:

Thanks for the reply.

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

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

Am I correct?

U can also use this command:

CREATE TABLE [TableName] LIKE [SourceTableName]

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

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

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.

[b] [font="verdana"]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[/font][/b]

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

[b][font="verdana"]
sql> create table def as (select * from abc [i]where 1=2[/i]);
table created. Sql> desc def name null? type ----------------------------------------- -------- ---------------------------- id number name varchar2(20) sql> select * from def; no rows selected[/font][/b] 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.

[B]Create table [tablename] like [sourcetablename] [/B]

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

[B]create table def as (select * from abc); [/B]

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.

[b] [font="verdana"]
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[/font][/b]

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,

[b][font="verdana"]
sql> create table def as (select * from abc [i]where 1=2[/i]);
table created.

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

sql> select * from def;
no rows selected[/font][/b]

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

[quote=NamrataSV;648]U can also use this command:

CREATE TABLE [TableName] LIKE [SourceTableName][/quote]

its wrong ...

[QUOTE=rakeshkamble;3170]its wrong ...[/QUOTE]
if you know the answer, please reply instead of just saying it's wrong...

[QUOTE=Beena;667]is it the same in Oracle and DB2... could some one explain in relats to different databases available...[/QUOTE]

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.

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

It is copy only structure of old table.

create table [B]new_table[/B] as select * from [B]existing_table[/B];

the above code will copy both data and structure

create table [B]new_table[/B] as select * from [B]existing_table[/B] 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.

[QUOTE=Allan Paul;412]Thanks for the reply.

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

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

Am I correct?[/QUOTE]

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

Hi

Try this following query

create table <Newtablename > as select *from <Data table name >
where <field_name> is null

the above query copied only data structure from Data table name

Try following query

create table destination_table_name as (select * from source_table_name);

eg:

create table new_emp as (select * from emp);

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

@zamary,

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

But, as you said the above query will never copy the constraints of what "[COLOR="Blue"]emp[/COLOR]" 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.

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 <tablename> as (select * from <tablename2> where 1=2)

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

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