locked
how to create temporary table as permanent table RRS feed

  • Question

  • Hello,

    Can I create temporary table as permanent table in sql server? So I don't want to lose any data which I inserted into temporary table? I don't want to remove that table and its data when I restart sql server.

    Is it possible?

    Thanks


    Virtual Reality



    • Edited by VR16 Monday, February 20, 2017 10:03 PM
    Monday, February 20, 2017 9:59 PM

Answers

  • You can create a table in model database, but that would make that table appear in every new database created on this instance, not just tempdb. Focus on what you are trying to achieve, why you want to store permanent data in tempdb in the first place, and maybe you will come up with an idea that does not use tempdb or model db at all.

    There are number of good readings about tempdb. One of them is Robert L Davis's "DEMYSTIFY TEMPDB PERFORMANCE AND MANAGEABILITY"

    http://sqlmag.com/site-files/sqlmag.com/files/uploads/2014/01/IderaWP_Demystifyingtempdb.pdf

    • Proposed as answer by Shanky_621MVP Tuesday, February 21, 2017 4:03 AM
    • Marked as answer by VR16 Tuesday, February 21, 2017 5:52 PM
    Tuesday, February 21, 2017 12:42 AM

All replies

  • Create a permanent table (normal table) and load your temp table into it or in the place when you are using your temp table to insert data (use permanent table instead).
    • Proposed as answer by Sergio S Arias Monday, February 20, 2017 10:41 PM
    Monday, February 20, 2017 10:04 PM
  • How would this differ from other user tables?

    Sometimes I need to persist temporary tables do disk and make them normal tables but include a spid column so I can delete from this table all the rows belonging to my spid. This way this table can be shared by multiple sessions. This does not scale well however.

    Monday, February 20, 2017 10:04 PM
  • Hi using trigger you can manage data in both table.

    Vignesh M


    • Edited by M.Vignesh Monday, February 20, 2017 10:14 PM
    Monday, February 20, 2017 10:13 PM
  • No, it is not possible. Tempdb purpose is as it's name reveals: for temporary stuff. It does not survive SQL restart by definition. Model database is copied over tempdb on each sql service restart. You would have to store data somewhere else to survive restart. For example, a special user database that is in simple recovery model, with delayed durability turned on, on fast disks. That would be somewhat closer to tempdb, and would survive SQL service restarts. Also, memory-optimized tables survive restarts if they are marked as SCHEMA_AND_DATA durable. The right solution depends on which problem are you trying to solve.
    • Proposed as answer by Naomi N Tuesday, February 21, 2017 5:05 AM
    Monday, February 20, 2017 11:11 PM
  • Thanks for your reply...

    Can I create a table in model database besides tempdb?

    Does model db change when I make changes to sql server?

    Do you have any good documentation which refresh my temporary database concept?

    Thanks


    Virtual Reality


    • Edited by VR16 Monday, February 20, 2017 11:51 PM
    Monday, February 20, 2017 11:47 PM
  • You can create a table in model database, but that would make that table appear in every new database created on this instance, not just tempdb. Focus on what you are trying to achieve, why you want to store permanent data in tempdb in the first place, and maybe you will come up with an idea that does not use tempdb or model db at all.

    There are number of good readings about tempdb. One of them is Robert L Davis's "DEMYSTIFY TEMPDB PERFORMANCE AND MANAGEABILITY"

    http://sqlmag.com/site-files/sqlmag.com/files/uploads/2014/01/IderaWP_Demystifyingtempdb.pdf

    • Proposed as answer by Shanky_621MVP Tuesday, February 21, 2017 4:03 AM
    • Marked as answer by VR16 Tuesday, February 21, 2017 5:52 PM
    Tuesday, February 21, 2017 12:42 AM

  • Do you have any good documentation which refresh my temporary database concept?

    Adding to what Vedran said please refer below

    Understanding Logging in Tempdb. Is Tempdb Recreated or Rebuilt after SQL Server Starts?


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, February 21, 2017 4:03 AM