Alter Table/Stored Procedure/Column Does not Exist

try again same thing i did here..   i am selecting same column which is added just now.....

itsvtk

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.

Try using Create table like SRigney said and if this doesn't work, try making #table a global temporary table ##table.

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, ...

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..

CREATE #tmp
go
ALTER TABLE #tmp ADD column_new int IDENTITY
go
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..

ok...you 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:

CREATE ##Tmp

declare @sql nvarchar(4000)

set @sql = ' ALTER TABLE ##tmp ADD column_new int IDENTITY '+char(13)+
            'select column_new from ##tmp'
execute  (@sql)

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.

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...)

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:
RecordDate
ProductType
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.

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
go

select * from my_table

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.

The clustered index does not guarentee order and does not work.  I've tried this.

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.