Geeks Talk

Prepare for your Next Interview




Creating a table by copying another table's structure

This is a discussion on Creating a table by copying another table's structure within the Oracle forums, part of the Databases category; How could I create a new table by copying another table's structure?...


Go Back   Geeks Talk > Databases > Oracle

Register FAQ Members List Calendar Mark Forums Read
  #1 (permalink)  
Old 05-31-2006
Junior Member
 
Join Date: May 2006
Posts: 9
Thanks: 0
Thanked 4 Times in 4 Posts
Allan Paul is on a distinguished road
Creating a table by copying another table's structure

How could I create a new table by copying another table's structure?
Reply With Quote
The Following User Says Thank You to Allan Paul For This Useful Post:
Sponsored Links
  #2 (permalink)  
Old 06-01-2006
Junior Member
 
Join Date: Apr 2006
Posts: 29
Thanks: 6
Thanked 12 Times in 5 Posts
pat.mclean is on a distinguished road
Re: Creating a table by copying another table's structure

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

Patrick.
Reply With Quote
  #3 (permalink)  
Old 06-01-2006
Junior Member
 
Join Date: May 2006
Posts: 9
Thanks: 0
Thanked 4 Times in 4 Posts
Allan Paul is on a distinguished road
Re: Creating a table by copying another table's structure

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?
Reply With Quote
  #4 (permalink)  
Old 06-16-2006
Junior Member
 
Join Date: Jun 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
NamrataSV is on a distinguished road
Re: Creating a table by copying another table's structure

U can also use this command:

CREATE TABLE [TableName] LIKE [SourceTableName]
Reply With Quote
  #5 (permalink)  
Old 06-18-2006
Contributing Member
 
Join Date: Sep 2005
Posts: 53
Thanks: 3
Thanked 6 Times in 6 Posts
Beena is on a distinguished road
Re: Creating a table by copying another table's structure

is it the same in Oracle and DB2... could some one explain in relats to different databases available...
Reply With Quote
  #6 (permalink)  
Old 06-19-2006
Junior Member
 
Join Date: Jun 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
NamrataSV is on a distinguished road
Re: Creating a table by copying another table's structure

This commands works for DB2...not sure about Oracle
Reply With Quote
  #7 (permalink)  
Old 06-19-2006
Expert Member
 
Join Date: Jun 2006
Location: India
Posts: 411
Thanks: 15
Thanked 33 Times in 25 Posts
jamesravid is on a distinguished road
Re: Creating a table by copying another table's structure

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.

Last edited by jamesravid : 06-19-2006 at 04:35 AM.
Reply With Quote
  #8 (permalink)  
Old 06-19-2006
Expert Member
 
Join Date: Jun 2006
Location: India
Posts: 411
Thanks: 15
Thanked 33 Times in 25 Posts
jamesravid is on a distinguished road
Re: Creating a table by copying another table's structure

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

Last edited by jamesravid : 06-19-2006 at 05:15 AM.
Reply With Quote
The Following User Says Thank You to jamesravid For This Useful Post:
  #9 (permalink)  
Old 11-28-2006
Junior Member
 
Join Date: Nov 2006
Location: india
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
rakeshkamble is an unknown quantity at this point
Re: Creating a table by copying another table's structure

Quote:
Originally Posted by NamrataSV View Post
U can also use this command:

CREATE TABLE [TableName] LIKE [SourceTableName]
its wrong ...
Reply With Quote
  #10 (permalink)  
Old 11-28-2006
Administrator
 
Join Date: May 2006
Location: New York, USA
Posts: 562
Thanks: 20
Thanked 1,311 Times in 77 Posts
admin has disabled reputation
Re: Creating a table by copying another table's structure

Quote:
Originally Posted by rakeshkamble View Post
its wrong ...
if you know the answer, please reply instead of just saying it's wrong...
Reply With Quote
  #11 (permalink)  
Old 12-04-2006
Expert Member
 
Join Date: Sep 2006
Location: India
Posts: 131
Thanks: 1
Thanked 20 Times in 20 Posts
Innila is on a distinguished road
Re: Creating a table by copying another table's structure

Quote:
Originally Posted by Beena View Post
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.
Reply With Quote
  #12 (permalink)  
Old 04-24-2007
Junior Member
 
Join Date: Apr 2007
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
yuvarajan2000 is on a distinguished road
Re: Creating a table by copying another table's structure

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

It is copy only structure of old table.
Reply With Quote
  #13 (permalink)  
Old 07-12-2007
Moderator
 
Join Date: Jun 2007
Location: Bangalore,India
Posts: 1,426
Thanks: 8
Thanked 125 Times in 112 Posts
debasisdas will become famous soon enoughdebasisdas will become famous soon enough
Re: Creating a table by copying another table's structure

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.
Reply With Quote

Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.1.0
Copyright © 2008 GeekInterview.com. All Rights Reserved