Solved

Alter Table/Stored Procedure/Column Does not Exist

Posted on 2004-10-20
19
259 Views
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?
Thanks
0
Comment
Question by:Fraser_Admin
  • 6
  • 6
  • 4
  • +3
19 Comments
 
LVL 14

Expert Comment

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

itsvtk
0
 
LVL 15

Expert Comment

by:SRigney
Comment Utility
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.
0
 

Expert Comment

by:a_tya
Comment Utility
Try using Create table like SRigney said and if this doesn't work, try making #table a global temporary table ##table.
0
 

Author Comment

by:Fraser_Admin
Comment Utility
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.
0
 

Expert Comment

by:a_tya
Comment Utility
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...
0
 
LVL 9

Expert Comment

by:konektor
Comment Utility
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, ...
0
 

Author Comment

by:Fraser_Admin
Comment Utility
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
0
 

Expert Comment

by:a_tya
Comment Utility
Got it..
Try this..

CREATE #tmp
go
ALTER TABLE #tmp ADD column_new int IDENTITY
go
select column_new from #tmp
0
 

Expert Comment

by:a_tya
Comment Utility
Sorry , this works in Query Analyser...If you need to use it in a stored procedure , I have to think of smth else..
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Expert Comment

by:a_tya
Comment Utility
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)
0
 

Author Comment

by:Fraser_Admin
Comment Utility
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.
0
 
LVL 12

Expert Comment

by:kselvia
Comment Utility
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...)
0
 

Author Comment

by:Fraser_Admin
Comment Utility
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.
0
 

Expert Comment

by:a_tya
Comment Utility
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.
0
 

Author Comment

by:Fraser_Admin
Comment Utility
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
0
 
LVL 12

Expert Comment

by:kselvia
Comment Utility
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.

0
 

Author Comment

by:Fraser_Admin
Comment Utility
The clustered index does not guarentee order and does not work.  I've tried this.
0
 
LVL 12

Expert Comment

by:kselvia
Comment Utility
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.
0
 
LVL 12

Accepted Solution

by:
kselvia earned 500 total points
Comment Utility
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.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now