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
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
What about transations?
BEGIN TRANSACTION;
ALTER TABLE Roles ADD ModifiedDate DateTime;
GO;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
INSERT INTO Roles (Name, [Description], CreatedBy, BuiltIn, Created, ModifiedDate) VALUES ('Name', 'Description', 0, 1, GETDATE(), GETDATE());
GO;
COMMIT TRANSACTION;
