Results 1 to 6 of 6

Thread: Create same table

  1. #1
    Expert Member
    Join Date
    Oct 2006
    Answers
    209

    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


  2. #2
    Expert Member
    Join Date
    Sep 2006
    Answers
    130

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


  3. #3
    Expert Member
    Join Date
    Nov 2006
    Answers
    518

    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.

  4. #4
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    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.


  5. #5
    Junior Member
    Join Date
    Jun 2007
    Answers
    1

    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.


  6. #6
    Junior Member
    Join Date
    Aug 2007
    Answers
    1

    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
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact