Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 alter n update temptable

Author  Topic 

ZaraAnn
Starting Member

2 Posts

Posted - 2012-09-25 : 00:20:41
Hi, I am facing with this kind of prob where I could not Alter and update temptable in 1 stored procedure(SP). in 1 SP, i pivot from 2 tables, insert data into temptable, then trying to insert 1 col and update it.

ALTER PROCEDURE [dbo].[uspgetdata]
@dataid nvarchar(50)

--pivot

ALTER TABLE ##TableTemp ADD dataID varchar(50);
UPDATE ##TableTemp SET dataID =@dataid ;

--full join table 1 and ##TableTemp

the error --> Invalid column name 'dataID'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-25 : 00:45:30
dynamic pivoting ?
it is easier this way
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

ZaraAnn
Starting Member

2 Posts

Posted - 2012-09-25 : 00:57:12
no khtan, actually pivoting done. error occurs when i'm trying to alter that ##TableTemp

my SP
-----

ALTER PROCEDURE [dbo].[uspgetdata]
@dataid nvarchar(50)

BEGIN
--pivoting table Done

--Trying to alter and update ##TableTemp
ALTER TABLE ##TableTemp ADD dataID varchar(50);
UPDATE ##TableTemp SET dataID =@dataid ;


*i'm trying to insert the dataID so that i can proceed with the inner join


--full join table 1 and ##TableTemp
END

thanks khtan, anyway i'll try to modified my SP based on the example page u gave.
Go to Top of Page
   

- Advertisement -