sql-server – 在创建表后调用insert时出现“无效的列名”错误

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)以及它们之间的外键关系.在这种情况下,我需要：

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

当我尝试执行此脚本时,我收到以下错误：

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

看起来SQL Server使用Table2的旧模式(实际上没有这些列),但如果表刚刚使用新模式创建,这怎么可能呢？

我究竟做错了什么？

服务器版本是SQL Server 2012(SP1) – 11.0.3128.0(X64).

UPDATE.

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

解决方法

您需要使用新定义将语句分组到新批处理中.如果您在SSMS中运行它,只需插入一个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,N'Default value');
PRINT(N'Default value was inserted in Table2.');
END

COMMIT

否则,您可以在子批处理中运行违规行

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!