ADD a column and UPDATE it - SQL Server Forums

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

ADD a column and UPDATE it

Author

Topic

Cornelius19
Starting Member

30 Posts

Posted - 2008-12-09 : 16:33:49

Hi,

Here is a simplified version of my script:


--Part One
CREATE TABLE [MyTable] (
	[word] [nvarchar] (50) COLLATE Latin1_General_CS_AS NULL 
) 

ALTER TABLE MyTable
ADD MyColumn nvarchar(1)

--Part Two
UPDATE MyTable
SET MyColumn = 0

I can execute it successively: part one first and part two after without error. However, when I try to execute the whole, I got an “Invalid column name 'MyColumn'.” message. Is there any way to run a script containing first an ADD command and than an UPDATE command?

Cornelius

P.-S. I know that there are many ways to reach the same results as the above script does without having ADD and UPDATE (like including the new column when the table is created). However, the actual script is more complex: the table is not created by CREATE but by INSERT * INTO (so I do not specify the columns at creation), the UPDATE does not involve all the rows but only specified rows (so I can not add a column with simply a default value), etc.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-09 : 16:44:33

You need to add a GO statement after the ALTER TABLE.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Cornelius19
Starting Member

30 Posts

Posted - 2008-12-09 : 17:19:19

Thanks, that works fine!

Cornelius

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources