We help IT Professionals succeed at work.

MS SQL Server 2008 Add Column and Set Value

jobprojn
jobprojn asked
on
When I run either set of code independently, they work find.  When I run them together I get the following error:

Invalid column name 'MyNewField'

Indeed,  MyNewField does not get added to the table.
--Set A
ALTER TABLE MyTable
ADD MyNewField tinyint NULL

--Set B	
UPDATE MyTable
SET MyNewField = AnotherFieldInMyTable

Open in new window

Comment
Watch Question

Commented:
Now try and run them together.

--Set A
ALTER TABLE MyTable
ADD MyNewField tinyint NULL
GO
--Set B      
UPDATE MyTable
SET MyNewField = AnotherFieldInMyTable
This is caused by precompilation.

If you try to run everything in one batch then the UPDATE command compilation must fail because it does not recognize the new column.

GO command splits the code into two separately compiled batches and the error "disappears".
Top Expert 2011
Commented:
you have to commit changes to a table before you can then use the changed object.
(thats what the go does)

Author

Commented:
The code without an explanation was only moderately helpful.  The additional comment really brought my understanding to where it needed to be to have it all make sense.

Commented:
Cool. Glad it was of help. :)
But please be careful, GO and COMMIT are not synonyms in T-SQL :-)