我正在使用SSMS开发sql脚本,它在数据库中进行了一些更改：
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.
解决方法
sql Server尝试编译整个批处理.如果表已经存在,那么它将根据预先存在的定义进行编译.引用新列的语句不会编译,因此批处理永远不会执行.
您需要使用新定义将语句分组到新批处理中.如果您在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'');')
