CODEWITHSUNDEEP
sql-server
Dzun Ho
Dzun Ho

Reputation: 367

When #temp table in SQL SERVER will be dropped?

I have a table "#tblResourceC7" like this:

Id          Class                Code                 Value
----------- -------------------- -------------------- -----------
1           F1                   A                    100
2           F1                   B                    100
3           F2                   C                    220
4           F3                   A                    150
5           F2                   C                    300
6           F3                   D                    120

Then I create a new table "#tblResource9" by run this query,

SELECT *
INTO #tblResource9
FROM
(
    SELECT Class, [A], [B], [C], [D] FROM
    (SELECT Class, Code, SUM(Value) AS SUM_VALUE 
        FROM #tblResourceC7 GROUP BY Class, Code) AS TBL7
    PIVOT
    (
        MAX(SUM_VALUE)
        FOR Code IN ([A], [B], [C], [D])
    ) AS PIVOTTABLE
)
AS KQ9

I have a result when "SELECT * FROM #tblResource9":

Class                A           B           C           D
-------------------- ----------- ----------- ----------- -----------
F1                   100         100         NULL        NULL
F2                   NULL        NULL        520         NULL
F3                   150         NULL        NULL        120

BUT, when I bring these code to a query string like this: 

DECLARE @QUERY91 nvarchar(MAX);

SET @QUERY91 = 'SELECT *
                INTO #tblResource9
                FROM
                (
                    SELECT Class, [A], [B], [C], [D] FROM
                    (SELECT Class, Code, SUM(Value) AS SUM_VALUE 
                       FROM #tblResourceC7 GROUP BY Class, Code) AS TBL7
                    PIVOT
                    (
                        MAX(SUM_VALUE)
                        FOR Code IN ([A], [B], [C], [D])
                    ) AS PIVOTTABLE
                )
                AS KQ9
                ';

EXEC (@QUERY91)

Everything work fine (3 row(s) affected). But why I don't have table "#tblResource9" when I "SELECT * FROM #tblResource9" (Invalid object name '#tblResource9').

Upvotes: 0

Views: 299

Answers (1)

James Z
James Z

Reputation: 12317

Temporary tables will be dropped when your session ends, except if it was created inside a stored procedure, then it will be dropped when you exit the procedure.

Create table in books online

A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.

All other local temporary tables are dropped automatically at the end of the current session.

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

Upvotes: 1

Related Questions