T-SQL – 為什麼 ALTER 和 UPDATE 語句只在它們自己的批處理中執行？

我有更新和更改語句，它們僅在放入它們自己的批處理時才執行。下面是我的精簡版。跳過程式碼並閱讀下面的問題可能是個好主意。

DROP TABLE #tmpImportData
USE database_name
GO

DECLARE @localVar VARCHAR(MAX) = 'data'
DECLARE @localVar VARCHAR(MAX) = 'data'
DECLARE @localVar VARCHAR(MAX) = 'data'
DECLARE @localVar int = 1
--Etc...

DECLARE @localVar INT
DECLARE @localVarVARCHAR(50)    
DECLARE @localVar AS BINARY (16)    
---Etc...


CREATE TABLE #tmpImportData (GuideFirstName VARCHAR(MAX), GuideLastName VARCHAR(MAX), 
                            email VARCHAR(MAX), group_id_text VARCHAR(MAX), CandidateName VARCHAR(MAX), 
                            grade_text VARCHAR(5), dateofbirth DATETIME

                            )

SET @sql = 'BULK INSERT #tmpImportData FROM '''+@fileName+''' WITH (FIELDTERMINATOR ='';'', ROWTERMINATOR = ''\n'', FIRSTROW = 1)'
EXEC (@sql)

--John O. How many rows from the import
SELECT @countCandidateImport = count(*) FROM #tmpImportData

--Fix some import data before any proccessing. We need to remove any punctuation, extra spaces, etc.
UPDATE #tmpImportData SET CandidateName = LTRIM(RTRIM(CandidateName)) FROM #tmpImportData
UPDATE #tmpImportData SET CandidateName = Replace(CandidateName,'.', '') FROM #tmpImportData
--UPDATE #tmpImportData SET CandidateName = Replace(CandidateName,',', '') FROM #tmpImportData

--Check DB for current district. If not exists create new one
SELECT @districtId = id, @ditrict_admins_restricted = CountAdminPermited, @candidates_restricted = count_candidate_restricted
FROM DistrictsTable WHERE name = RTRIM(LTRIM(@districtName))


--John O. What is whytry?
IF @districtId IS NULL
BEGIN
   INSERT INTO districtstable (name, active, CountAdminPermited, why_try_permitted) VALUES (RTRIM(LTRIM(@districtName)), 1, 3, @iswhyTry)
   SELECT @districtId = SCOPE_IDENTITY()
END
ELSE BEGIN
   SELECT @count_created_district_admins = count(*) 
   FROM GuidesTable gt
   INNER JOIN guides_to_permission gtp ON gtp.guide_id = gt.ID
   WHERE gt.DistrictID = @districtId AND gtp.permission_id = 0 AND gt.Active = 1

   IF(@count_created_district_admins >= @ditrict_admins_restricted)
   BEGIN
       SET @messageID = 1
   END

   SELECT @count_created_candidates = count(*)
   FROM CandidatesTable ct
   WHERE ct.DistrictID =  @districtId AND ct.Active = 1
   IF @count_created_candidates + @countCandidateImport >= @candidates_restricted
   BEGIN
       SET @messageID = 2  
   END

   IF(@messageID <> -1)
       RETURN
END
select 1
--Create School
DECLARE @schoolId AS INT
SET @schoolId = NULL
SELECT @schoolId = SchoolsTable.ID FROM SchoolsTable WHERE SchoolsTable.Name = LTRIM(RTRIM(@schoolName))
IF @schoolId IS NULL
   BEGIN
       INSERT INTO schoolstable (districtid, name, Active) VALUES (@districtId, @schoolName, 1)
       SELECT @schoolId = SCOPE_IDENTITY()
   END
ELSE 

SET @adminGuideId = NULL
SELECT @adminGuideId = GuidesTable.ID FROM GuidesTable WHERE LTRIM(RTRIM(FirstName)) = LTRIM(RTRIM(@FirstName)) 
   AND LTRIM(RTRIM(LastName)) = LTRIM(RTRIM(@LastName)) AND LTRIM(RTRIM(EmailAddress)) = LTRIM(RTRIM(@adminLogin))
select @adminGuideId
IF @adminGuideId IS NULL 
BEGIN
       SET @password = HASHBYTES('MD5', @passwordAdmin)                

       INSERT INTO GuidesTable (DistrictID, ClassID, FirstName         , LastName          , EmailAddress, [Password], createddate, Administrator, Active, website_access_level_id)
       VALUES                  (@districtId, 0     , @FirstName        , @LastName         , @adminLogin , @password , GetDate()  , 1            , 1     , 1)
       SET @tmpGuideId = SCOPE_IDENTITY()
       INSERT INTO guides_to_permission (guides_to_permission.guide_id ,guides_to_permission.permission_id) 
       VALUES                           (@tmpGuideId                   ,0                 )
       INSERT INTO guides_to_school_access (guides_to_school_access.guide_id , guides_to_school_access.school_id) 
       VALUES                              (@tmpGuideId                      , @schoolId                        )
END
ELSE BEGIN
       SELECT @AccessToSchoolId = gtsa.id FROM guides_to_school_access gtsa WHERE @adminGuideId = gtsa.guide_id AND @schoolId = gtsa.school_id
       IF @AccessToSchoolId IS NULL
       BEGIN
           INSERT INTO guides_to_school_access (guides_to_school_access.guide_id , guides_to_school_access.school_id) 
           VALUES                              (@adminGuideId                    , @schoolId                        )
       END
END
GO
--Create unique guides
--declare @SQL1 nvarchar(max)
--set @SQL1 = '
ALTER TABLE #tmpImportData ADD group_id INT
ALTER TABLE #tmpImportData ADD guide_id INT
ALTER TABLE #tmpImportData ADD password_plain_text VARCHAR(500)
ALTER TABLE #tmpImportData ADD guide_email VARCHAR(500)
ALTER TABLE #tmpImportData ADD class_id INT 

--exec sp_executesql @SQL1      
GO

UPDATE #tmpImportData SET group_id = CAST(group_id_text AS INT)
UPDATE #tmpImportData SET group_id = 0 WHERE group_id IS NULL

SELECT * FROM #tmpImportData
GO

因此，表格可以使用上面的程式碼進行更新和更改。但是，如果我刪除GO最後一個語句之前的兩個語句，並在選擇之後保留最後一個語句。該表未更新或更改。知道為什麼會這樣嗎？

此外，如果我使用 NOGO語句執行此程序，則除了更改、更新和選擇之外，所有內容都將執行。

我懷疑這裡沒有您看到的數據庫錯誤。

如果您GO在兩個最終語句之前刪除該UPDATE語句，我會想像查詢會因“未知列’group_id’”之類的內容而崩潰。

發生這種情況是因為每次都會為每個批次生成執行計劃，即每次通過GO. 根據我的經驗，該計劃並不總是考慮進行模式更改的語句（SELECT .. INTO操作和索引操作除外）。如果您嘗試在語句實際執行之前生成包含該group_id列的查詢計劃，則該列ALTER TABLE實際上還不存在。

我最好的建議可能不適用於所有可能的情況，但它適用於您的情況：從一開始就正確創建臨時表，在這種情況下，通過在頂部ADD包含原始語句中的列，這樣您就不會了CREATE TABLE不必ALTER稍後在程式碼中使用它。

引用自：https://dba.stackexchange.com/questions/89665