为什么这个TSQL会失败？

Question

IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE Name=N'isHidden' AND Object_ID=Object_ID(N'Templates'))
BEGIN
    BEGIN TRANSACTION
    GO
        CREATE TABLE dbo.Tmp_Templates
            (
            ID int NOT NULL IDENTITY (1, 1),
            isHidden bit NULL,
            FileName nvarchar(255) NOT NULL,
            Name nvarchar(255) NOT NULL,
            Description nvarchar(1024) NULL,
            UploadedByTVDBUsersID int NOT NULL,
            Created datetime NOT NULL
            )
        GO
        SET IDENTITY_INSERT dbo.Tmp_Templates ON
        GO
        IF EXISTS(SELECT * FROM dbo.Templates)
             EXEC('INSERT INTO dbo.Tmp_Templates (ID, FileName, Name, Description, UploadedByTVDBUsersID, Created)
                SELECT ID, FileName, Name, Description, UploadedByTVDBUsersID, Created FROM dbo.Templates WITH (HOLDLOCK TABLOCKX)')
        GO
        SET IDENTITY_INSERT dbo.Tmp_Templates OFF
        GO
        DROP TABLE dbo.Templates
        GO
        EXECUTE sp_rename N'dbo.Tmp_Templates', N'Templates', 'OBJECT' 
        GO
        ALTER TABLE dbo.Templates ADD CONSTRAINT
            PK__Templates__499219E9 PRIMARY KEY CLUSTERED 
            (
            ID
            )
        GO
        PRINT N'  Templates ADD isHidden'
    COMMIT
END

错误结果：

Msg 102，级别15，状态1，第7行语法不正确，靠近“TRANSACTION”。注意:更改对象名称的任何部分都可能破坏脚本和存储过程。

更新：

不包括封装事务的IF语句，此SQL由生成。

如果我删除包装If语句，那么所有操作都正常，但只有在字段还没有出现时，才需要进行更改。如何使IF语句正常工作？

嗯..。为什么-1和投票结束？

Answer 1

我必须将事务的每一部分封装在IF语句中，这样GOs就不会嵌入到IF语句中。下面的TSQL工作得很好。事务按预期更新架构。

BEGIN TRANSACTION
GO
    IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE Name=N'isHidden' AND Object_ID=Object_ID(N'Templates'))
    BEGIN
        CREATE TABLE dbo.Tmp_Templates
            (
            ID int NOT NULL IDENTITY (1, 1),
            isHidden bit NULL,
            FileName nvarchar(255) NOT NULL,
            Name nvarchar(255) NOT NULL,
            Description nvarchar(1024) NULL,
            UploadedByTVDBUsersID int NOT NULL,
            Created datetime NOT NULL
            )
        ALTER TABLE dbo.Tmp_Templates ADD PRIMARY KEY (ID)
    END
    GO
    IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE Name=N'isHidden' AND Object_ID=Object_ID(N'Templates'))
    BEGIN
        SET IDENTITY_INSERT dbo.Tmp_Templates ON
    END
    GO
    IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE Name=N'isHidden' AND Object_ID=Object_ID(N'Templates'))
    BEGIN
        IF EXISTS(SELECT * FROM dbo.Templates)
             EXEC('INSERT INTO dbo.Tmp_Templates (ID, FileName, Name, Description, UploadedByTVDBUsersID, Created)
                SELECT ID, FileName, Name, Description, UploadedByTVDBUsersID, Created FROM dbo.Templates WITH (HOLDLOCK TABLOCKX)')
    END
    GO
    IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE Name=N'isHidden' AND Object_ID=Object_ID(N'Templates'))
    BEGIN
        SET IDENTITY_INSERT dbo.Tmp_Templates OFF
    END
    GO
    IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE Name=N'isHidden' AND Object_ID=Object_ID(N'Templates'))
    BEGIN
        DROP TABLE dbo.Templates
    END
    GO
    IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE Name=N'isHidden' AND Object_ID=Object_ID(N'Templates'))
    BEGIN
        EXECUTE sp_rename N'dbo.Tmp_Templates', N'Templates', 'OBJECT' 
        PRINT N'  Templates ADD isHidden'
    END
    GO
COMMIT

Answer 2

首先，GO语句将查询拆分到

IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE Name=N'isHidden' AND Object_ID=Object_ID(N'Templates'))
BEGIN
    BEGIN TRANSACTION
    //error - END missing

还有没有BEGIN的END关键字。您需要删除GO语句。

更新：

IF 1 = 1
BEGIN
    SELECT * FROM someTable
    GO
END

还生成Msg 102，15级，状态1，第3行不正确的语法在“某个表”附近。

Answer 3

SqlServer不允许您在事务中使用sp_rename，因为它会严重破坏事务。

您可以再次删除和添加表，在这种情况下，您还可以使用临时表来执行查询工作，截断旧表，并将行从temp移到模板中。

Sample temp table
CREATE TABLE #myTempTable
(
  DummyField1 INT,
  DummyField2 VARCHAR(20)
)

参考http://msdn.microsoft.com/en-us/library/ms188351.aspx