doesn't see column after alter table add column name

try adding this at the beginning:

if object_id('tempdb..#temp1') is not null
drop table #temp1

run that at first and then try it.

good catch...i was assuming that the column was added successfully.

yes i can. i know for sure it will work. it just doesn't the column. i just found a way to get around it. this is what i did

after altering the table do
select * into #temp2 from #temp1

then select * from #temp2 then that column will be available.

Can post the entire query list as you are not adding the column to the table. the answer was for the issue what you had in hand. I believe I am right

i feel i was right

I feel you were as well.

it worked for me. there was no error return. when i do a select all, that temp table has the new column in there.

select * into #temp1

alter #temp1 add row_id as in identity

select * into #temp2 from #temp1 where row_id = 1

this worked fine.
what i have problem before was that instead of putting it into #temp2 i just went ahead and did select * from #temp1 where row_id = 1 that was when the invalid column error popped up. but with temp2 in place. the error disappeared.

it worked for me. there was no error return. when i do a select all, that temp table has the new column in there.

select * into #temp1

alter #temp1 add row_id as in identity

select * into #temp2 from #temp1

select * from #temp2 where row_id = 1

this worked fine.
what i have problem before was that instead of putting it into #temp2 i just went ahead and did select * from #temp1 where row_id = 1 that was when the invalid column error popped up. but with temp2 in place. the error disappeared.

Im sure your solution did work fine...BUT udayakumarlm gave a correct solution so you can't just close out the question.

you can not add identity to the temp table
alter #temp1 add row_id as int identity
this will result error
Msg 8183, Level 16, State 1, Line 2
Only UNIQUE or PRIMARY KEY constraints can be created on computed columns, while CHECK, FOREIGN KEY, and NOT NULL constraints require that computed columns be persisted.

you are skipping the error trace to the second second line.

the above is his error not mine. i didn't have problem with altering the table. my error was trying to select the table with where caluse where the new column = to a variable. i didn't have problem with altering the table. so how is that a right solution?

there is no way one can execute the statements without getting an error. my answer was right.

sorry to say but there was no error return when i ran the alter table add column.

this is what you are saying that is working
select * into #temp1
alter table #temp1 add row_id as int identity
select * into #temp2 from #temp1
select * from #temp2 where row_id = 1

let us know which application you are using that is not giving any error

microsoft sequel server 2000?

select * into #temp1 from student

ALTER TABLE #temp1 ADD Row_ID int identity

select * into #temp2 from #temp1

select * from #temp2 where row_id = 1

and these are the correct codes

it looks like we are wasting time here. I believe something and you believe something. i will accept you are right as it does not make any loss than time waste to both of us

your post went right to how i can't add column in a temp table useing alter. I didn't say i have a problem with that and never said that was my problem my problem was only it couldn't see the column after i altered. but if i do select * then it will display that column. It only has an error when there was a where clause like ( where row_id = 1) but once you insert all to temp2 that will be fine.

well hold on. let me try to set up pc to pc and i'll show you. believe me if you are right i'll give you point. ok so hold on. i might need your email so if you can provide me an email i'll send you the link to connect to my pc ok? let me know if it's ok before i do it.

this is what you said is working
alter #temp1 add row_id as int identity
for that only I replied stating that it is an error.
this is what is corrected by u
ALTER TABLE #temp1 ADD Row_ID int identity
judge if i am wrong or right.
regards

ok i see what  you are saying. yes i was wrong but that was just a mistyped . but in the question i didn't say i have an error at the line did i? i only said after i altered and do select, it return invalid column.

it looks like we were arguing on the wrong thing. you were working on one version of the query and i was working on the version you posted as part of the query.

This is the right answer based on the query posted in the question. It was my fault not to check the posted query. but the solution to fix is
select * into #temp1 from Student
ALTER TABLE #temp1 ADD Row_ID int identity
select * into #temp2 from #temp1 -----this line is needed because sql doesn't see the column somehow so you can't use where clause here
select * from #temp2 where row_id = 1 or whatever

sorry, for being arrogant, joined a course on avoiding arguments it looks like it is not effective enough. thanx

i was being arrogant too. sorry for that. i should have checked my question before i posted.