Sql Server 2000 - Alter Table + Insert Into = Errors?

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, Buil

Solution 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

ALTERTABLE 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

Solution 2:

What about transations?

BEGIN TRANSACTION;
ALTERTABLE Roles ADD ModifiedDate DateTime;
GO;
COMMIT TRANSACTION;

BEGIN TRANSACTION;
INSERTINTO Roles (Name, [Description], CreatedBy, BuiltIn, Created, ModifiedDate) VALUES ('Name', 'Description', 0, 1, GETDATE(), GETDATE());
GO;
COMMIT TRANSACTION;