Create same table

how to create a table same as the existing one but no data?

This is the question asked by visitor surender reddy

In Oracle, use the following query,
create table emp_new as select * from emp where 1=2;

This query will create the table's structure alone. If the 'where' condition is removed, the data of the emp table will also be created.

*** Mangai Varma ***

The above sql command (given by 'mangai varma') is correct.

Further explanation:

create table emp_new as select * from emp where 1=2;

the above command is executed in following 5 stages:

1) new table emp_new is created with blank structure.
2) the select statement is evaluated to fetch records from emp table. But, it gives no records coz, the ‘where’ condition given here will not satisfy for any of the records in ‘emp’ table.
3) the structure of result of select statement is copied into the newly created table emp_new.
4) the result records of select statement are now copied into the newly created table emp_new. But, there are no records in the result of select statement.
5) thus, the new table emp_new remains with just the structure and no records.

Any further clarifications? feel free to ask:)

try this following query

create table new_table as select * from old_table where 1=2;

Note --instead of using 1=2 u can also use any statment that evalutes to false.

This SQL command is not working with SQL server 2005.
What would be the alternative to this SQL command for SQL Server.

To create table without the records from the source table, the [B]select [/B]statement must have a [B]where[/B] clause that should specify a condition that [U]cannot[/U] be satisfied

syntax: create table <new table name > as select from <old table name >
where <condition (cannot satisfy) i.e, wrong condition>