dynamic SQL Create Table

The following dynamic SQL script works for creating a table on the fly but if I change the select @.tmpTblNm = 'tmpABC' to select @.tmpTblNm = '#tmpABC'
it will not create the temp table. Can anyone help on creating a temp table dynamiclly?

declare @.tmpTblNm varchar(40),
@.str varchar(1000)

select @.tmpTblNm = 'tmpABC'
select @.str = ''

-- Create a temp table to hold the current page of data
-- Add an ID column to track the current row
select @.str = 'Create Table '+ @.tmpTblNm +
' ( row_id int IDENTITY PRIMARY KEY,
customerID int,
customerName varchar(40),
address1 varchar(40),
city varchar(25) )'

exec (@.str)The problem is that it did exactly what you requested, just not what you wanted.

A temporary table lasts for the duration of its creator. For a script, that is as long as you have the session open (until you close the session). For a stored procedure, the duration is as long as the procedure runs. For dynamic SQL, the duration is the dynamic execute.

When you build a temp table dynamically, the table exists for as long as the statement runs. You can use a global (aka ##) table, but that brings its own problems to play. You can create a table within your script or procedure, but then it isn't very dynamic.

-PatP