Adding column to table and then setting value gives error

Question

I am pretty new to SQL. I am working with SQL Server 2012. I need to do the following: add a column to an existing table and fill all rows in that column with the same value. To do this, I have come up with the following based on searching online:

ALTER TABLE myTable ADD myNewColumn VARCHAR(50) NULL
UPDATE myTable SET myNewColumn = 'test'

The problem is that in SQL server, I get the following error for the second statement:

Invalid column name 'myNewColumn

So, my guess is that a new column called myNewColumn wasn't created by the first statement.

Answer 1

You need to perform the update in a separate batch. Otherwise SQL Server tries to parse and validate that the column exists before ever trying to run the ALTER that creates it. You get the invalid column name at parse time, not at run time.

One workaround is to use GO between the two batches:

ALTER TABLE dbo.myTable ADD myNewColumn VARCHAR(50) NULL;
GO
UPDATE dbo.myTable SET myNewColumn = 'test';

(Always use schema prefixes to reference objects and always terminate statements with semi-colons.)

However this only works in Management Studio and other certain client applications, because it is not actually part of the T-SQL language; these client tools see it as a batch separator and it tells them to submit and evaluate these two batches separately. It will NOT work in code blocks submitted to SQL Server in other ways and as a single batch, e.g. in the body of a stored procedure:

CREATE PROCEDURE dbo.foo
AS
BEGIN
  SET NOCOUNT ON;
  SELECT 1;
  GO
  SELECT 2;
END
GO

This yields the following errors, because it actually splits the stored procedure into two separate batches:

Msg 102, Level 15, State 1, Procedure foo, Line 8
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'END'.

What you can do as a different workaround is force the update into its own batch by executing it in dynamic SQL.

DECLARE @sql NVARCHAR(MAX), @value VARCHAR(50) = 'test';

ALTER TABLE dbo.myTable ADD myNewColumn VARCHAR(50) NULL;

SET @sql = N'UPDATE dbo.myTable SET myNewColumn = @value;';

EXEC sp_executesql @sql, N'@value VARCHAR(50)', @value;

(Why you should use EXEC sp_executesql vs. EXEC(@sql).)

Another workaround; perform the add and the update in one step:

ALTER TABLE dbo.myTable ADD myNewColumn VARCHAR(50) DEFAULT 'test' WITH VALUES;

(You can later drop the default constraint if you don't actually want any future rows to inherit that value in circumstances that would cause that behavior.)

Answer 2

Place the word GO after your alter statement