Create Temp Table Using Dynamic SQL

Question

I wanted to create many temp tables in a loop and insert data, but when I ran a test with this simple dynamic SQL, it ran successfully, but there was no tabled created. Could someone please help me? Thank you in advance.

IF OBJECT_ID('tempdb..#blankOuputLevel2') IS NOT NULL
BEGIN
    DROP TABLE #blankOuputLevel2
END

DECLARE @sql NVARCHAR(MAX)
SET @sql = 'CREATE TABLE #blankOuputLevel2 (tjob NVARCHAR(30));'
EXEC sp_executesql @sql

This code runs successfully.

Then I run the SELECT shown below, and get an error:

Invalid object name '#blankOuputLevel2'

SELECT * FROM #blankOuputLevel2

Answer 1

Well, this is how (local) temporary tables #Temp work.

They are automatically dropped when they get out of scope.

Your temp table is created within the context / scope of the stored procedure sp_executesql and it is dropped automatically when this stored procedure returns.

You can try to use global temporary tables ##Temp, but as Aaron Bertrand pointed out: "Global temp tables are only useful when you can guarantee single concurrency; if two users run the same code and reference ##whatever, they’re going to trample all over each other. And when you can guarantee single concurrency, I’d just use a permanent table."

In many cases you can put all your code in the dynamic SQL string to execute as a single batch by a single call to sp_executesql:

DECLARE @sql NVARCHAR(MAX)
SET @sql = 'CREATE TABLE #blankOuputLevel2 (tjob NVARCHAR(30)); '
SET @sql = @sql + 'INSERT INTO #blankOuputLevel2 .....;'
SET @sql = @sql + 'SELECT ... FROM #blankOuputLevel2 .....;'
SET @sql = @sql + '... the rest of your logic using #blankOuputLevel2 ...'
EXEC sp_executesql @sql

In this case the local temp table #blankOuputLevel2 will be visible to the code that you put in the @sql and it will be dropped after sp_executesql finishes. You can run several instances of this dynamic code simultaneously and each instance will get its own separate temp table.

---

For more details about various temporary tables in SQL Server have a look at this question Local and global temporary tables in SQL Server:

• Table variables (DECLARE @t TABLE) are visible only to the connection that creates it, and are deleted when the batch or stored procedure ends.

• Local temporary tables (CREATE TABLE #t) are visible only to the connection that creates it, and are deleted when the connection is closed.

• Global temporary tables (CREATE TABLE ##t) are visible to everyone, and are deleted when all connections that have referenced them have closed.

• Tempdb permanent tables (USE tempdb CREATE TABLE t) are visible to everyone, and are deleted when the server is restarted.

Temporary tables

Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE:

• 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 can't 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.