temporary tables in sql server 2005 how persistent is it

Posted on 2009-02-17
Last Modified: 2012-05-06
on a sql agent job created 2 steps
step1 creates temporary tables by running sql script
step2 reads these temporary tables, does some processing and writes to a new table

what i found was, as soon as the step1, exists, the temporary tables disappears

how persistent are the temporary tables. can it be used across different sessions

Question by:Greens8301
    LVL 39

    Accepted Solution

    Temporary tables go out of scope when the connection is disconnected.  If Storedprocedure1 creates a temp table, and it then calls StoredProcedure2, StoredProcedure2 can access it.  When Step 1 exits, the table goes out of scope and is dropped so Step 2 cannot access it.
    LVL 60

    Assisted Solution

    global temp tables (prefixed with ##), go out to scope when ALL connections using them go out of scope

    Author Comment

    Does global temporary table work across steps in the SQL Agent job

    "ALL connections using them go out of scope"
    When step1 completes, there are no connects to the tables. Does this constitute ALL connections
    Then when step2 starts, (probably a different session id), can it see GTT


    LVL 60

    Expert Comment

    yes, in that case it would go out to scope (even the global)...why not use a user-table instead?

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now