“无效的列名"创建表后调用插入时出错 [英] "Invalid column name" error when calling insert after table created

问题描述

我正在使用 SSMS 开发 SQL 脚本，这会对数据库进行一些更改:

I'm developing SQL script, using SSMS, which makes some changes in database:

USE MyDatabase;

BEGIN TRANSACTION;

-- some statements

PRINT(N'#1');

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'Table1' AND COLUMN_NAME = 'Table2_Id'))
BEGIN
    ALTER TABLE [dbo].[Table1] DROP CONSTRAINT [FK_Table1_Table2_Table2_Id];
    ALTER TABLE [dbo].[Table1] DROP COLUMN [Table2_Id];
    DROP TABLE [dbo].[Table2];

    PRINT(N'Table2 was dropped.');
END

PRINT(N'#2');

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'Table2'))
BEGIN
    CREATE TABLE [dbo].[Table2]
    (
        [Id] INT NOT NULL PRIMARY KEY IDENTITY,
        [Number] INT NOT NULL UNIQUE,
        [Name] NVARCHAR(200) NOT NULL,
        [RowVersion] TIMESTAMP NOT NULL
    );
PRINT(N'Table2 was re-created.');
    INSERT INTO [dbo].[Table2]([Number], [Name]) VALUES(-1, N'Default value');
PRINT(N'Default value was inserted in Table2.');
END

-- some statements

COMMIT TRANSACTION;

如果Table1有一列名为Table2_Id，那么数据库有两个表(Table1 和Table2)以及它们之间的外键关系.在这种情况下，我需要:

If Table1 has a column, named Table2_Id, then database has two tables (Table1 and Table2) and a foreign key relationship between them. In that case, I need to:

• 删除外键关系FK_Table1_Table2_Table2_Id;
• 删除外键列Table1.Table2_Id;
• drop Table2;
• 重新创建Table2，使用新的表模式；
• 在 Table2 中插入一些默认值.

• drop foreign key relationship FK_Table1_Table2_Table2_Id;
• drop foreign key column Table1.Table2_Id;
• drop Table2;
• re-create Table2, using new table schema;
• insert some default value in Table2.

当我尝试执行此脚本时，出现以下错误:

When I'm trying to execute this script, I'm getting these errors:

消息 207，级别 16，状态 1，第 262 行无效的列名称编号".
消息 207，级别 16，状态 1，第 262 行无效的列名称名称".

Msg 207, Level 16, State 1, Line 262 Invalid column name 'Number'.
Msg 207, Level 16, State 1, Line 262 Invalid column name 'Name'.

看起来 SQL Server 使用了 Table2 的旧架构(它确实没有这些列)，但是如果表刚刚使用新架构创建，这怎么可能?

Looks like SQL Server uses old schema for Table2 (which indeed hasn't these columns), but how is this possible, if the table has just created with new schema?

我做错了什么?

服务器版本为 SQL Server 2012 (SP1) - 11.0.3128.0 (X64).

Server version is SQL Server 2012 (SP1) - 11.0.3128.0 (X64).

更新.

我添加了 PRINT 调用(参见上面的脚本).消息窗口中没有任何内容，除了错误消息.所以，脚本没有被执行......发生了什么??

I've added PRINT calls (see script above). There's nothing in message window, except error messages. So, the script isn't being executed... What's going on??

推荐答案

SQL Server 尝试编译整个批处理.如果该表已经存在，那么它将根据预先存在的定义进行编译.引用新列的语句不会编译，因此批处理永远不会执行.

SQL Server tries to compile the whole batch. If the table already exists then it will compile according to the pre-existing definition. The statement referencing the new columns doesn't compile and so the batch never executes.

您需要将使用新定义的语句分组到一个新批次中.如果您在 SSMS 中运行它，只需插入一个 GO

You need to group the statements using the new definition into a new batch. If you are running this in SSMS just insert a GO

USE MyDatabase;

BEGIN TRANSACTION;

-- some statements

PRINT(N'#1');

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'Table1' AND COLUMN_NAME = 'Table2_Id'))
BEGIN
    ALTER TABLE [dbo].[Table1] DROP CONSTRAINT [FK_Table1_Table2_Table2_Id];
    ALTER TABLE [dbo].[Table1] DROP COLUMN [Table2_Id];
    DROP TABLE [dbo].[Table2];

    PRINT(N'Table2 was dropped.');
END

GO

PRINT(N'#2');

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'Table2'))
BEGIN
    CREATE TABLE [dbo].[Table2]
    (
        [Id] INT NOT NULL PRIMARY KEY IDENTITY,
        [Number] INT NOT NULL UNIQUE,
        [Name] NVARCHAR(200) NOT NULL,
        [RowVersion] TIMESTAMP NOT NULL
    );
PRINT(N'Table2 was re-created.');
    INSERT INTO [dbo].[Table2]([Number], [Name]) VALUES(-1, N'Default value');
PRINT(N'Default value was inserted in Table2.');
END

COMMIT

否则你可以在子批次中运行违规行

Otherwise you could run the offending line in a child batch

    EXEC(N'INSERT INTO [dbo].[Table2]([Number], [Name]) VALUES(-1, N''Default value'');')

这篇关于“无效的列名"创建表后调用插入时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！