• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 571
  • Last Modified:

doesn't see column after alter table add column name

i did a select * into #temp1 from Student
then i do a alter #temp1 add row_id as int identity

the i do a select * from #temp1 where row_id = 2

but it keeps saying invalid column name row_id why is this?
0
StewSupport
Asked:
StewSupport
  • 12
  • 9
  • 4
1 Solution
 
chapmandewCommented:
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.
0
 
udaya kumar laligondlaTechnical LeadCommented:
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.
0
 
chapmandewCommented:
good catch...i was assuming that the column was added successfully.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
StewSupportAuthor Commented:
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.
0
 
udaya kumar laligondlaTechnical LeadCommented:
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
0
 
udaya kumar laligondlaTechnical LeadCommented:
i feel i was right
0
 
chapmandewCommented:
I feel you were as well.
0
 
StewSupportAuthor Commented:
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.
0
 
StewSupportAuthor Commented:
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.
0
 
chapmandewCommented:
Im sure your solution did work fine...BUT udayakumarlm gave a correct solution so you can't just close out the question.
0
 
StewSupportAuthor Commented:
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?
0
 
udaya kumar laligondlaTechnical LeadCommented:
there is no way one can execute the statements without getting an error. my answer was right.
0
 
StewSupportAuthor Commented:
sorry to say but there was no error return when i ran the alter table add column.
0
 
udaya kumar laligondlaTechnical LeadCommented:
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
0
 
StewSupportAuthor Commented:
microsoft sequel server 2000?
0
 
StewSupportAuthor Commented:
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
0
 
udaya kumar laligondlaTechnical LeadCommented:
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
0
 
StewSupportAuthor Commented:
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.
0
 
StewSupportAuthor Commented:
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.
0
 
udaya kumar laligondlaTechnical LeadCommented:
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
0
 
StewSupportAuthor Commented:
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.
0
 
udaya kumar laligondlaTechnical LeadCommented:
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.
0
 
StewSupportAuthor Commented:
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
0
 
udaya kumar laligondlaTechnical LeadCommented:
sorry, for being arrogant, joined a course on avoiding arguments it looks like it is not effective enough. thanx
0
 
StewSupportAuthor Commented:
i was being arrogant too. sorry for that. i should have checked my question before i posted.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 12
  • 9
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now