Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
plahanov
Starting Member
1 Post |
 Posted - 2012-08-07 : 02:58:05
|
| 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 headersSELECT * 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 Plahanov |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-08-07 : 03:23:41
|
It has to do with scope.if you create the temporary table in the dynamic sql, the table is destroyed when the scope ends.
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-07 : 10:00:17
|
| you should be creating temporary table outside of dynamica sql and populating it with exec(@sql) call------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Create Temp Table Dynamically