Add a new column in temp table in sp cannot be used immediately

I created a temp table in my stored procedure and then added a new identity column to it.

However, I am not able to use this new column immediately, it says column not found.

SELECT * INTO #temp FROM table_name

ALTER TABLE #temp ADD __Identity int IDENTITY(1,1)

SELECT * FROM #temp WHERE __Identity >= 10

Here __Identity column is not found. If I just did SELECT * FROM #temp without the where clause, the final result does have the __Identity column correctly added to the table. Why can't I query it?

Thanks!

That is a good question. I originally had the problem, but then it just went away and I can't get it to come back again. From Management Studio, try placing a "GO" between each statement. That's what I did to get the problem to go away and can't get it to come back again. Are you issuing these to SQL Server from 3 different command objects on the same connection?

|||

All these statements are in one stored procedure, so "GO" is not going to work. Thanks!

|||

Hi,

Based on my understanding ,these three statements will be pre-checked at the same time, and then each statement will be executed. The third statement can't pass the check because the first two statements has't been executed.

I suggest you to write seperate these two SP, one is for creating a column, another is for query.

create proc CreateColumnas ALTER TABLE table ADD _identityint IDENTITY(1,1) go --For the CreatingColumn exec procfinal --execute the CreateColumn SP.create proc QueryResultas select *from table where _identity>'10'goexec QueryResult

Thanks.