Urgent...... - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Urgent......

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-08-16 : 09:24:26

Praveen Kumar writes "Dear Sir,

Actually i want to create temporary table with identity column at runtime. below query table name is dynamic and columns are dynamic that why i have taken it as paramertic query. i am not able to create single hash temporary. below code is giving object #temp table not found. please help me

declare @sql as varchar(1000)
SET @sql = 'SELECT PKeyID = IDENTITY (int, 1, 1) ,WING_DESC INTO #TempTable FROM PI_WING_MSTS ORDER BY WING_DESC'
exec(@sql)
Select * from #TempTable"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-16 : 09:28:09

Local temp tables (single #) ceases to exist outside the context in which they are created, in your case outside exec() statement. So do this...

declare @sql as varchar(8000)
SET @sql = 'SELECT PKeyID = IDENTITY (int, 1, 1) ,WING_DESC INTO #TempTable FROM PI_WING_MSTS ORDER BY WING_DESC; Select * from #TempTable'
exec(@sql)

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-16 : 12:18:42

http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources