ALTER do not work on temporary table
-
I try to alter a temporary table on runtime by adding an identity field.
My code is like this:
SELECT MSG INTO #TMPTBL FROM DBO.ORIGINALTABLE WHERE 1=0
ALTER TABLE #TMPTBL ADD AUTOID INT IDENTITY(1,1)
SELECT * FROM #TMPTBL WHERE AUTOID = 1
The error message is "Invalid column AUTOID". The strange thing is when I select like below, there are two columns come out including AUTOID column:
SELECT * FROM #TMPTBL
The same message will be appeared when I select like below:
SELECT AUTOID FROM #TMPTBL
Anyone out there can explain what going on and how to alter the temporary table in a right way.
Regards,
kokyan 
-
You should be able to add cols to temp tables. Try this... put in batch separates between your statements.... like below:
SELECT MSG INTO #TMPTBL FROM DBO.ORIGINALTABLE WHERE 1=0
go
ALTER TABLE #TMPTBL ADD AUTOID INT IDENTITY(1,1)
go
SELECT * FROM #TMPTBL WHERE AUTOID = 1
Hope that helps
Billy
-
Thanks for your reply. I had tried this method before but seem do not work. Do the ALTER command alter the temporary table out from my current session? I really confuse!
Regards, kokyan -
If you want to avoid the requirement for batch separators, you could create the IDENTITY field in the SELECT INTO statement. EG:
SELECT IDENTITY(INT, 1,1) AS AUTOID, MSG INTO #TMPTBL FROM DBO.ORIGINALTABLE WHERE 1=0
Cheers,
- Mark
Cheers,
- Mark -
Thanks for the method. It works!
Regards, kokyan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply