Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2004
    Posts
    11

    Unanswered: Creating a blank table from another

    Hi again,

    I want to create a blank table with the same column names as another but with no rows i.e. it is empty. Does anyone know how to do the last part?

    I have so far

    create table newtest as select * from shared.test ..... (empty bit here?)

    Any good advanced sql tutorial urls would be good to.

    Thanks in advance

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    select *
    from OldTable
    into Newtable
    where 1 = 0

    This will not copy triggers, indexes, and the such. Just column names and datatypes.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Posts
    11
    Ok i will try that but why does that work?

    Cheers

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    select *
    from OldTable
    into Newtable

    ...selects columns from the old table into a Newtable (created on the fly).

    where 1 = 0

    ...always evaluates to false (in my universe, but I'm a Democrat), so no rows match the filter criteria and thus no rows are actually inserted.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Posts
    11
    Cheers, Thanks


  6. #6
    Join Date
    Feb 2004
    Posts
    41
    U can try like This Also

    create table NewTable
    as select * from OldTable
    where rownum < 1;

    Even this will not copy triggers, Indexes and Constraints....
    'A candle will loose nothing by lighting an another candle'

  7. #7
    Join Date
    Feb 2004
    Posts
    4

    Talking copy with indexes, PK, FK, triggers...

    if you need to copy the table as is (with all indexes, primary/foreign keys, triggers), you can use the option "generate sql script":

    Enterprise Manager > Server > DataBase > Tables > put your cursor on the selected table/s > All Tasks (right mouse click) > Generate SQL Script

    Then you get a wizard which allow you to create scripts that drops/create your selected objects. on the options tab you may decide if you would like to have the indexes, triggers, relationships...

    If you want to create the same table BUT with different name, make sure you rename all the objects (indexes, primary/foreign keys, triggers) before running.

    good luck

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Sowmyam
    U can try like This Also

    create table NewTable
    as select * from OldTable
    where rownum < 1;

    Even this will not copy triggers, Indexes and Constraints....
    Ahhhh...the smell of Oracle (Or is it UDB) in the morning...it smells like.....confusion

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99
    AS 
    	SELECT * FROM Orders
    	 WHERE rownum < 1;
    GO


    And nope...that won't work...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2004
    Posts
    41

    Thumbs up

    Hi Brett,
    I didn't get u clearly, are u telling

    create table NewTable
    as select * from OldTable
    where rownum < 1;

    this code won't work.

    I tried it works.
    Ok this query Creates a Table NewTable of same structure as OldTable and NewTable will not have any rows.
    This won't copy any indxes, constraints and all, but creates a table.

    pls give a feedback for this.

    Thanks In Advance.
    'A candle will loose nothing by lighting an another candle'

  10. #10
    Join Date
    Feb 2004
    Posts
    41
    Hi Brett,
    I am very sorry. yes it is confusion.
    it will work in Oracle, this is Microsoft SQL server, by thinking it is Oracle i replied.

    I am very sorry about it.
    'A candle will loose nothing by lighting an another candle'

  11. #11
    Join Date
    Dec 2003
    Location
    Delhi INDIA
    Posts
    58

    Talking

    hi
    just add truncate command after your code!!

    hope this will solve ur issue.

    Cheers

    Deepak K
    Deep..

    MCDBA
    ------------------------------------------
    How much data you can afford to lose??

  12. #12
    Join Date
    Nov 2011
    Posts
    1

    working fine . I created several blank tables through the wizards. Thanks again.

    Quote Originally Posted by shlomi_salmi View Post
    if you need to copy the table as is (with all indexes, primary/foreign keys, triggers), you can use the option "generate sql script":

    Enterprise Manager > Server > DataBase > Tables > put your cursor on the selected table/s > All Tasks (right mouse click) > Generate SQL Script

    Then you get a wizard which allow you to create scripts that drops/create your selected objects. on the options tab you may decide if you would like to have the indexes, triggers, relationships...

    If you want to create the same table BUT with different name, make sure you rename all the objects (indexes, primary/foreign keys, triggers) before running.

    good luck
    several blank tables through the wizards. Thanks again.

  13. #13
    Join Date
    Feb 2004
    Posts
    492
    select top 0 *
    into Newtable
    from OldTable


    the execution plan shows no difference versus doing a where 1 = 0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •