SQL Server 2000 - ALTER TABLE + INSERT INTO = Errors?

Question

I'm trying to alter a table to add a new column, then insert a new row into it.

ALTER TABLE Roles ADD ModifiedDate DateTime;
INSERT INTO Roles (Name, [Description], CreatedBy, BuiltIn, Created, ModifiedDate)
    VALUES ('Name', 'Description', 0, 1, GETDATE(), GETDATE())

but I get:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'ModifiedDate'.

when i try to run the above SQL in SQL Server Management Studio. I think this is a Studio error, not a server error. If the SQL was run, it should work as the column would exist at that point.

How can I add a new column to a table and then insert into that table?

Versions:

• SQL Server 2000
• SQL Server Management Studio 2008

Answer 1

As expected. SQL Server does not execute line by line. It compiles and parse the batch, and when this happens the column does not exist.

You need to decouple the 2 actions thus

ALTER TABLE Roles ADD ModifiedDate DateTime;
EXEC ('
    INSERT INTO Roles (Name, [Description], CreatedBy, BuiltIn, Created, ModifiedDate)
    VALUES (''Name'', ''Description'', 0, 1, GETDATE(), GETDATE())
')

A "GO" is a batch separator only for client tools and is not recognised by the server