My requirement is to create a temp table and insert data into the temp table dynamically ( all sql statements are inside the string variable and execute it finally) .
I tried the following
DECLARE @SQL VARCHAR(200)
SET @SQL = 'CREATE TABLE #TEMPPDET (ANULVESAL NUMERIC(16,4),BASIC NUMERIC(16,4)) SELECT * FROM #TEMPPDET' SELECT @SQL
EXEC(@SQL) -- here it returns the table headers
SELECT * FROM #TEMPPDET -- shows error Invalid object name '#TEMPPDET'.
The above script successfully execute and returns the results from the temp table (for the select query in the @sql variable )
But when I execute the SELECT * FROM #TEMPPDET statement after the EXEC(@SQL) it shows some error.
So the scope of the temp table is only inside the variable ( string)? How can I create a temp table dynamically like the above example and can able to use the temp table
Create Temp Table Dynamically
Posted - 08/07/2012 : 02:58:05
