What command is used to create a table by copying the structure of another table?

Answer :

CREATE TABLE .. AS SELECT command

Explanation :

To copy only the structure, the WHERE clause of the SELECT command should contain a FALSE statement as in the following.

CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2;

If the WHERE condition is true, then all the rows or rows satisfying the condition will be copied to the new table.

  • Interview Candidate
  • 9
  • 23035

Showing Answers 1 - 9 of 9 Answers

deepali gupta

  • Sep 13th, 2005
 

but if don't use WHERE clause then what will happen???

  Was this answer useful?  Yes

mukesh negi

  • Sep 18th, 2005
 

create a table y as same structure as table x with zero records

create table x as select * from y where 1=2;

  Was this answer useful?  Yes

mukesh negi

  • Sep 18th, 2005
 

select level,max(sal) from emp

where level=&n connect by prior sal>sal

group by level;

  Was this answer useful?  Yes

manish joshi

  • Sep 20th, 2005
 

My Ans is in support of mr. Negi  and this sql query is absolutely working fine with oracle data base but with sql server we can do the same task in some different way...like......

Select col1,col2,col3 into newtabname from oldtabname 

to copy some perticular column.this query create the structure and copy the values into the table named here "newtabname" from the old table named "oldtabname"

if we want to copy all the  data from oldtabname to newtabname

put the "*" in place of col1,col2.....

Thanks

  Was this answer useful?  Yes

komal

  • Dec 29th, 2005
 

create a table y as same structure as table x with zero records

create table x as select * from y where 1=2;

I am completely agree with this.

  Was this answer useful?  Yes

satish

  • Feb 20th, 2006
 

I think we can also use

select * into newtable where 1=2

  Was this answer useful?  Yes

vijay

  • Jul 4th, 2006
 

Hi Satish,

Only part of your statement is correct

i.e., In SQL Server, use

Select * into new_table_name from old_table_name where 1 = 0

  Was this answer useful?  Yes

padma.rkp

  • Apr 28th, 2007
 

IN ORACLE:
create table new_table_name as
select col1,col2 from old_table_name
where 1=2
IN SQL SERVER
Select * into new_table_name from old_table_name where 1 = 2

  Was this answer useful?  Yes

Bhuvaneswari.V

  • Aug 22nd, 2009
 

SELECT * INTO NewTable FROM ExistingTable WHERE 1 = 2 will copy the structure of the existing table without any data. If we need the NewTable with data we can filler the records in WHERE clause.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions