November 25, 2003 at 4:55 am
the code below produces a simple resultset with 2 cols: Col1 and TEMPCOL
However when I include a WHERE clause (where TEMPCOL =2) I get an error message:
Invalid column name 'TEMPCOL'.
Any help is appreciated.
------------------------------------
create table TableX (Col1 int)
insert TableX (Col1) values (30)
insert TableX (Col1) values (40)
insert TableX (Col1) values (50)
-- fill a temp table with data from TableX
select * into #temptable from TableX
-- add a new identitly column to the temp table
alter TABLE #temptable add TEMPCOL int identity (1,1) NOT NULL
select * from #temptable
--where TEMPCOL =2
drop table TableX
drop table #temptable
---------------------------------
November 25, 2003 at 5:07 am
SELECT *, TEMPCOL=Identity(int,1,1)
INTO #TempTable
FROM TableX
select * from #temptable
where TEMPCOL =2
drop table TableX
drop table #temptable
November 25, 2003 at 5:10 am
Could use exec to insert column
exec ('alter TABLE #temptable add TEMPCOL int identity (1,1) NOT NULL ') but GRN's solution is better.
Far away is close at hand in the images of elsewhere.
Anon.
November 25, 2003 at 5:31 am
Thanks GRN and DavidBurrows, both solutions work very well.
I am a little surprised that
exec ('alter TABLE #temptable add TEMPCOL int identity (1,1) NOT NULL ')
works diferently to
alter TABLE #temptable add TEMPCOL int identity (1,1) NOT NULL
why does the existence of the exec command make a difference?
November 25, 2003 at 10:57 pm
You could have got the correct result if you placed a GO between the Alter and the Select as well. The Alter had to be executed in a batch before the Select would see the new column. The EXEC(Alter ...) caused the Alter to run in a separate batch. So when the Select executed, the TempCol had been added to the table.
Jeff
November 26, 2003 at 2:25 am
quote:
...if you placed a GO between the Alter and the Select...
True in this contrived example but not if the intention was to use it in a procedure.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply