-
Expert Member
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
-
Expert Member
Re: Create same table
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 ***
-
Expert Member
Re: Create same table
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
Last edited by sutnarcha; 12-11-2006 at 04:50 AM.
-
Junior Member
Problem Creating same table
This SQL command is not working with SQL server 2005.
What would be the alternative to this SQL command for SQL Server.
-
Re: Create same table
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.
-
Junior Member
Re: Create same table
To create table without the records from the source table, the select statement must have a where clause that should specify a condition that cannot be satisfied
syntax: create table as select from
where
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
Reply With Quote