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

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

Patrick.

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?

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.

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.

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

its wrong ...

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

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 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.