How to make the new column available to upate? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to make the new column available to upate?

Author

Topic

annie_bai@hotmail.com
Starting Member

6 Posts

Posted - 2008-01-02 : 16:34:09

I have a store procedure to create a table temp, populate data from Origin1 table, and then add a new column SSN to temp table, update the SSNs from another table. but the following code gives error, pleaes help!

Create table temp (
....
)
Insert into temp 
select * from Origin1

Alter table temp ADD Name int

Update temp
Set SSN = (select SSN from Origin2
where id = Origin.id)

it gives 'Invalid column name: 'SSN'
I am aware that the add new column doesn't take effect until end of the SP, but there must be a way, Thanks for any inputs.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-02 : 16:56:16

Looks like the column SSN isnt in the table. SQL Server is complaining about SSN not Name.

>>I am aware that the add new column doesn't take effect until end of the SP, but there must be a way, Thanks for any inputs.
No. You can use the column right away.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

annie_bai@hotmail.com
Starting Member

6 Posts

Posted - 2008-01-02 : 17:03:41

quote:
Originally posted by dinakar

Looks like the column SSN isnt in the table. SQL Server is complaining about SSN not Name.

No, the columns is not added.
But how to enforce the column to be added before the 'Update' statement?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-02 : 17:31:23

Can you post the entire code from the create table script?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2008-01-02 : 19:19:24

Alter table temp ADD SSN int
?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-02 : 19:20:15

Maybe:

Alter table temp ADD SSN Name int

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources