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),
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,
city varchar(25) )'
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.