Dynamic SQL table creation - invalid object after execution?

Question

I am trying to create a table that dynamically pulls the starting IDENTITY ID based on a variable from another table. The SQL executes successfully but afterwards, I am unable to find my temporary table. The DBCC CHECKIDENT brings back Invalid object name '#address_temp'.

IF OBJECT_ID('tempdb.dbo.#address_temp', 'U') IS NOT NULL 
   DROP TABLE #address_temp

DECLARE @address_temp_ID VARCHAR(MAX)

SET @address_temp_ID = (SELECT MAX(ID) FROM [PRIMARYDB].[dbo].[ADDRESS])

DECLARE @SQLBULK VARCHAR(MAX)
SET @SQLBULK = 'CREATE TABLE #address_temp(
   [ID] [int] IDENTITY(' + @address_temp_ID + ',1)  NOT NULL,
    [NAME] [varchar](128) NOT NULL,
    [ADDRESS1] [varchar](128) NOT NULL,
    [ADDRESS2] [varchar](128) NULL,
    [CITY] [varchar](128) NULL,
    [STATE_ID] [smallint] NULL,
    [ZIP] [varchar](10) NOT NULL,
    [COUNTY] [varchar](50) NULL,
    [COUNTRY] [varchar](50) NULL
CREATE CLUSTERED INDEX pk_add ON  #address_temp ([NAME])'

EXEC (@SQLBULK)

DBCC CHECKIDENT('#address_temp')

The local temp table is automatically dropped when exiting the batch sent to the EXEC statement. If you want to use the table, all the code using the table must be in the string @SQLBULK and executed at the same time. — Shannon Severance
– Shannon Severance, Commented Nov 17, 2015 at 18:54

Hogan · Accepted Answer · 2015-11-19 14:44:32Z

2

Table names that start with # are temporary tables and SQL Server treats them differently. First of all they are only available to the session that created them (this is not quite true since they you can find them in the temp name space but they have a unique system generated name)

In any case they won't persist so you don't need to drop them (that happens auto-magically) and you certainly can't look at them after your session ends.... they are gone.

Don't use a temp table, take out the # in the name. Things will suddenly start working.

edited Nov 19, 2015 at 14:44

answered Nov 17, 2015 at 18:28

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic SQL table creation - invalid object after execution?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related