Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Alter Table/Stored Procedure/Column Does not Exist

Posted on 2004-10-20
19
Medium Priority
?
308 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
[X]
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
  • 6
  • 6
  • 4
  • +3
19 Comments
 
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.....

itsvtk
0
 
LVL 15

Expert Comment

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

Expert Comment

by:a_tya
ID: 12357660
Try using Create table like SRigney said and if this doesn't work, try making #table a global temporary table ##table.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

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

Expert Comment

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

Expert Comment

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

Author Comment

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

Expert Comment

by:a_tya
ID: 12369110
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
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..
0
 

Expert Comment

by:a_tya
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:

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
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.
0
 
LVL 12

Expert Comment

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

Author Comment

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

Author Comment

by:Fraser_Admin
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
go

select * from my_table
0
 
LVL 12

Expert Comment

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

0
 

Author Comment

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

Expert Comment

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

Accepted Solution

by:
kselvia earned 1500 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.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

618 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