Alter Table/Stored Procedure/Column Does not Exist

I have a stored procedure which I'm building a temporary table (# table).  I add an id column to the table by the alter table statement.  I then try to select this column.  When I do, I get an error column does not exist.  But if I select * I can see the column.

Any idea?
Who is Participating?
Ken SelviaConnect With a Mentor RetiredCommented:
I should add that as long as there is no WHERE clause that would make the optimizer use another index, and in this case there is not.
Thandava VallepalliCommented:
try again same thing i did here..   i am selecting same column which is added just now.....

Have you tried using Create Table to build the temp table prior to populating it?
You can then create the ID column in the create table statement.
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Try using Create table like SRigney said and if this doesn't work, try making #table a global temporary table ##table.
Fraser_AdminAuthor Commented:
I can select it when doing a create table, but I don't want to do that.  I want to alter the table at a latter point and add the column.
I did exactly what you did (or at least I think so..) and I didn't receive any errors. I can't reproduce your error.

select *
into #tmp
from table1

ALTER TABLE #tmp ADD column_new VARCHAR(20) NULL

select column_new from #tmp

Maybe you have smth else in your code that ends the session and you lose your temp table... Send that part of the code between the creation of temp table and select of column...
check case sensitivity.
in oracle ... if u :
alter table Test add "Idf" number(10)
u can do only :
select "Idf" from Test, but cannot select Idf from Test, ...
but if u create case un-sensitive column name, using :
alter table Test add Idf number(10)
u can use :
select Idf from Test, select IDF from test, select idf from test, select IdF from test, ...
Fraser_AdminAuthor Commented:
I'm in sqlserver.  I'm doing:
create #tmp

then later I'm doing
alter table #tmp add column_new int identity

select column_new from #tmp
Got it..
Try this..

ALTER TABLE #tmp ADD column_new int IDENTITY
select column_new from #tmp
Sorry , this works in Query Analyser...If you need to use it in a stored procedure , I have to think of smth else..
a_tyaCommented: need to have the alter statement in another scope.  In order for you to do that you need global temporary tables (##tmp), because #tmp tables are valid only in the curent scope.
try dynamic query ...smth like this:


declare @sql nvarchar(4000)

set @sql = ' ALTER TABLE ##tmp ADD column_new int IDENTITY '+char(13)+
            'select column_new from ##tmp'
execute  (@sql)
Fraser_AdminAuthor Commented:
Yea, I don't want global tables.  So that is not an option.

Do you know of a way to do a cursor on about 20000 records quickly.  I tried a cursor on it, and it was very slow.  I need to select based on an order and do an update.
Ken SelviaRetiredCommented:
Why are you adding the column after you create the table?  Dynamic DDL is problematic.

You can add an identity when it is created.  I assume you are selecting into instead of creating and therein lies your quandry.

select *, column_new = identity(int,1,1)
into #temp_table
From MyTable
Where ....

There may be an even better way to do your 20000 row update.  Post the update statement you have if you want us to look at it.  (You never know what we might come up with...)
Fraser_AdminAuthor Commented:
I'm trying to create an inventory report based on the amount of products I have coming in.
The user specifies a date.
I get all products coming into the location and taken from the location.
I get all inventory records that have been done on the location.
SORT THIS TABLE ON ProductType, DATE!!!!!! (I think I can do this by inserting these records into a new table, which has an identity field, by using an order by)

The table I'm creating needs to be a temporary table of some sort (there is a possibility that more than one user can run this process at any time).
This table is based on live data for reporting, but it does not alter any live data.

My table structure is this:
RecordType (Inventory, Receiving, Leaving)
Amount (Inventory = amount of last inventory, Receiving = amount received on this load, Leaving = amount leaving on this load)
YTD Amount (Amount on this Record + YTD on last record)

YTD Amount = Inventory Amount on an inventory record (ie. An inventory resets the YTD Amount and all loads arriving and leaving are based on the last inventory)

That being said....I need to have my table ordered as I do an update since I'm relying on the last record's amount.

Is there anyway to do this.  Cursors are too slow!!!!  I need to somehow have an update which allows an order by statement, since SQL Server does not guarentee a sort order.  

This is also being doing in a stored procedure.  I want to return a recordset.
I don't have much time but if you want an update with a sort by try smth like this:

Update t
Set column = value
from table t
Order by /group by or whatever u need .

this is like a SELECT STATEMENT but it is un update.
Fraser_AdminAuthor Commented:
I get incorrect syntax near order by

This is my statement on a small test table.  My table has myField int and myField2 int.  myTable has 7 records.  myField is set 1 thru 7.

declare @myCount as int
set @myCount = 0

update a
set @myCount = myField2 =  myField + @myCount
from my_table a
order by myField

select * from my_table
Ken SelviaRetiredCommented:
Create a clustered index on the fields you want to ORDER BY then UPDATE will update in order by those fields.  You can't have an ORDER BY in an UPDATE statement.

Fraser_AdminAuthor Commented:
The clustered index does not guarentee order and does not work.  I've tried this.
Ken SelviaRetiredCommented:
I'd like to see the example because I have tried it too and was not able to come up with a scenario where it did not work.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.