SQL Server 2000 - ALTER TABLE + INSERT INTO = 错误? [英] 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, BuiltIn, Created, ModifiedDate)
    VALUES ('Name', 'Description', 0, 1, GETDATE(), GETDATE())

但我明白了:

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

当我尝试在 SQL Server Management Studio 中运行上述 SQL 时.我认为这是 Studio 错误，而不是服务器错误.如果运行了 SQL，它应该可以正常工作，因为此时该列会存在.

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?

版本:

• SQL Server 2000
• SQL Server 管理工作室 2008

推荐答案

正如预期的那样.SQL Server 不会逐行执行.它编译并解析批处理，当发生这种情况时，列不存在.

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())
')

GO"是仅用于客户端工具的批处理分隔符，服务器无法识别

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

这篇关于SQL Server 2000 - ALTER TABLE + INSERT INTO = 错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！