Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Alter Table/Stored Procedure/Column Does not Exist

Posted on 2004-10-20
Last Modified: 2012-06-27
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?
Question by:Fraser_Admin
  • 6
  • 6
  • 4
  • +3
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 12357341
try again same thing i did here..   i am selecting same column which is added just now.....

LVL 15

Expert Comment

ID: 12357496
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.

Expert Comment

ID: 12357660
Try using Create table like SRigney said and if this doesn't work, try making #table a global temporary table ##table.
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.


Author Comment

ID: 12358389
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.

Expert Comment

ID: 12358705
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...

Expert Comment

ID: 12358813
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, ...

Author Comment

ID: 12368931
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

Expert Comment

ID: 12369110
Got it..
Try this..

ALTER TABLE #tmp ADD column_new int IDENTITY
select column_new from #tmp

Expert Comment

ID: 12369175
Sorry , this works in Query Analyser...If you need to use it in a stored procedure , I have to think of smth else..

Expert Comment

ID: 12369521
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:


declare @sql nvarchar(4000)

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

Author Comment

ID: 12383623
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.
LVL 12

Expert Comment

ID: 12386741
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...)

Author Comment

ID: 12422367
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.

Expert Comment

ID: 12422477
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.

Author Comment

ID: 12445521
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
LVL 12

Expert Comment

ID: 12445593
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.


Author Comment

ID: 12474026
The clustered index does not guarentee order and does not work.  I've tried this.
LVL 12

Expert Comment

ID: 12474039
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.
LVL 12

Accepted Solution

kselvia earned 500 total points
ID: 12474060
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.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Save conflict messages on existing documents 15 74
MySQL Backup Strategy 15 44
Database Design Dilemma 6 58
Xenapp 7 creating SQL databases using generated script 7 32
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question