SQL Server 2000 - 更改表 +插入 = 错误？

发布于 2024-10-15 02:07:04 字数 590 浏览 1 评论 0原文

我正在尝试更改表以添加新列，然后在其中插入新行。

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，它应该可以工作，因为该列此时就存在。

如何将新列添加到表中，然后插入到该表中？

版本：

SQL Server 2000
SQL Server Management Studio 2008

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

仙女 2024-10-22 02:07:04

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

您需要将这两个操作解耦，因此

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

“GO”是仅适用于客户端工具的批次分隔符，服务器无法识别

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

回复收藏 0 原文

π浅易 2024-10-22 02:07:04

那么交易呢？

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;

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;

回复收藏 0 原文

~没有更多了~