none
temporary tables in SQL Server 2008

    Question

  • Are there any changes in the rules for creating temporary tables in SQL Server 2008 comparing to 2005? 2008 example in 'SELECT INTO' explicitly uses 'tempdb' while in 2005 any database was OK. Is this just an illustration or change in the features?

     

    Thanks

    Monday, October 18, 2010 3:30 PM

Answers

  • I think I found the example you are referring to. In the 2005 version, the example used tempdb to check for the prior existence of the temp table, and then dropped it. The example then did a USE AdventureWorks before creating the temp table. In 2008, no USE for the new database is done, and the select into fully qualifies the source table name with the name of the database.

    Both examples could work on either version.

    There is no difference in the way SELECT INTO a temp table works between 2005 and 2008.


    HTH, Kalen Delaney www.SQLServerInternals.com
    Monday, October 18, 2010 3:59 PM
    Moderator

All replies

  • There is no change in the behavior of temporary tables in 2008.

    Tom

    Monday, October 18, 2010 3:38 PM
  • No, there is no change in creating temporary tables.

    All you have to do is specify SELECT * INTO #table to create temporary tables. Temp tables are created in tempdb in all the versions.


    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    Monday, October 18, 2010 3:40 PM
  • I think I found the example you are referring to. In the 2005 version, the example used tempdb to check for the prior existence of the temp table, and then dropped it. The example then did a USE AdventureWorks before creating the temp table. In 2008, no USE for the new database is done, and the select into fully qualifies the source table name with the name of the database.

    Both examples could work on either version.

    There is no difference in the way SELECT INTO a temp table works between 2005 and 2008.


    HTH, Kalen Delaney www.SQLServerInternals.com
    Monday, October 18, 2010 3:59 PM
    Moderator
  • Thanks for the confirmation.
    Monday, October 18, 2010 4:50 PM