临时表嵌套查询更新报错：新增列提示无效列名

你遇到的这个问题其实是SQL Server的批处理编译顺序在搞鬼，我给你理清楚原因和解决办法：

问题原因 #

SQL Server会在执行整个SQL批处理之前，先对所有语句进行编译。当编译器处理你的UPDATE语句时，前面那些ALTER TABLE新增列的操作还没实际执行——也就是说，编译阶段临时表#temp_polelbyregion的结构还是修改前的状态，编译器根本看不到你刚加的那些列，自然就抛出"无效列名"的错误了。

而操作普通表时没问题，是因为普通表的结构在编译前就已经存在于数据库中，编译器能直接识别到所有列。

解决方案 #

方案1：用GO分隔批处理（最推荐） #

把修改表结构的语句和更新语句分成不同的批处理，这样前面的ALTER TABLE执行完成、临时表结构更新后，再编译和执行UPDATE语句，就能识别到新增的列了：

ALTER TABLE #temp_polelbyregion ALTER COLUMN REGION varchar(100)
ALTER TABLE #temp_polelbyregion ADD [Coordinate] float null
ALTER TABLE #temp_polelbyregion ADD [Street] float null
ALTER TABLE #temp_polelbyregion ADD [Block] float null
ALTER TABLE #temp_polelbyregion ADD [Postcode] float null
ALTER TABLE #temp_polelbyregion ADD [City] float null
ALTER TABLE #temp_polelbyregion ADD [County] float null
ALTER TABLE #temp_polelbyregion ADD [Cresta] float null
ALTER TABLE #temp_polelbyregion ADD [State] float null
ALTER TABLE #temp_polelbyregion ADD [Country] float null
ALTER TABLE #temp_polelbyregion ADD [None] float null
ALTER TABLE #temp_polelbyregion ADD [KnownCON] float null
ALTER TABLE #temp_polelbyregion ADD [KnownYB] float null
ALTER TABLE #temp_polelbyregion ADD [KnownHGT] float null
ALTER TABLE #temp_polelbyregion ADD [KnownOCC] float null

GO -- 这里分隔批处理，确保ALTER生效后再执行UPDATE

UPDATE #temp_polelbyregion 
SET Coordinate = x.Coordinate 
    ,Street = x.Street 
    ,Block = x.Block 
    ,Postcode = x.Postcode 
    ,City = x.City 
    ,County = x.County 
    ,Cresta = x.Cresta 
    ,State = x.State 
    ,Country = x.Country 
    ,None = x.None 
    ,KnownCON = x.KnownCON 
    ,KnownYB = x.KnownYB 
    ,KnownHGT = x.KnownHGT 
    ,KnownOCC = x.KnownOCC 
FROM ( 
    SELECT ACCGRPID 
        ,REGION 
        ,Coordinate = SUM(CASE WHEN GeoResolutionCode in (1) THEN LOCVAL ELSE 0 END) 
        ,Street = SUM(CASE WHEN GeoResolutionCode in (2,3) THEN LOCVAL ELSE 0 END) 
        ,Block = SUM(CASE WHEN GeoResolutionCode in (4) THEN LOCVAL ELSE 0 END) 
        ,Postcode = SUM(CASE WHEN GeoResolutionCode in (5,6) THEN LOCVAL ELSE 0 END) 
        ,City = SUM(CASE WHEN GeoResolutionCode in (7,8) THEN LOCVAL ELSE 0 END) 
        ,County = SUM(CASE WHEN GeoResolutionCode in (9) THEN LOCVAL ELSE 0 END) 
        ,Cresta = SUM(CASE WHEN GeoResolutionCode in (11) THEN LOCVAL ELSE 0 END) 
        ,State = SUM(CASE WHEN GeoResolutionCode in (10,12) THEN LOCVAL ELSE 0 END) 
        ,Country = SUM(CASE WHEN GeoResolutionCode in (14) THEN LOCVAL ELSE 0 END) 
        ,None = SUM(CASE WHEN GeoResolutionCode in (0) THEN LOCVAL ELSE 0 END) 
        ,KnownCON = SUM(CASE WHEN [BLDGSCHEME] <> 'RMS IND' AND [BLDGCLASS] = '0' THEN 0 ELSE LOCVAL END) 
        ,KnownYB = SUM(CASE WHEN YEAR([YEARBUILT]) = 9999 THEN 0 ELSE LOCVAL END) 
        ,KnownHGT = SUM(CASE WHEN [NUMSTORIES] = 0 THEN 0 ELSE LOCVAL END) 
        ,KnownOCC = SUM(CASE WHEN [OCCTYPE] = '0' THEN 0 ELSE LOCVAL END) 
    FROM #temp_locs 
    GROUP BY ACCGRPID, REGION 
) x 
INNER JOIN #temp_polelbyregion y ON x.REGION = y.REGION AND x.ACCGRPID = y.ACCGRPID

方案2：使用动态SQL执行UPDATE #

动态SQL的编译会推迟到执行阶段，此时临时表的结构已经更新，编译器能识别到新增列：

ALTER TABLE #temp_polelbyregion ALTER COLUMN REGION varchar(100)
ALTER TABLE #temp_polelbyregion ADD [Coordinate] float null
-- 省略其他ADD语句...

EXEC sp_executesql N'
UPDATE #temp_polelbyregion 
SET Coordinate = x.Coordinate 
    ,Street = x.Street 
    ,Block = x.Block 
    ,Postcode = x.Postcode 
    ,City = x.City 
    ,County = x.County 
    ,Cresta = x.Cresta 
    ,State = x.State 
    ,Country = x.Country 
    ,None = x.None 
    ,KnownCON = x.KnownCON 
    ,KnownYB = x.KnownYB 
    ,KnownHGT = x.KnownHGT 
    ,KnownOCC = x.KnownOCC 
FROM ( 
    SELECT ACCGRPID 
        ,REGION 
        ,Coordinate = SUM(CASE WHEN GeoResolutionCode in (1) THEN LOCVAL ELSE 0 END) 
        ,Street = SUM(CASE WHEN GeoResolutionCode in (2,3) THEN LOCVAL ELSE 0 END) 
        ,Block = SUM(CASE WHEN GeoResolutionCode in (4) THEN LOCVAL ELSE 0 END) 
        ,Postcode = SUM(CASE WHEN GeoResolutionCode in (5,6) THEN LOCVAL ELSE 0 END) 
        ,City = SUM(CASE WHEN GeoResolutionCode in (7,8) THEN LOCVAL ELSE 0 END) 
        ,County = SUM(CASE WHEN GeoResolutionCode in (9) THEN LOCVAL ELSE 0 END) 
        ,Cresta = SUM(CASE WHEN GeoResolutionCode in (11) THEN LOCVAL ELSE 0 END) 
        ,State = SUM(CASE WHEN GeoResolutionCode in (10,12) THEN LOCVAL ELSE 0 END) 
        ,Country = SUM(CASE WHEN GeoResolutionCode in (14) THEN LOCVAL ELSE 0 END) 
        ,None = SUM(CASE WHEN GeoResolutionCode in (0) THEN LOCVAL ELSE 0 END) 
        ,KnownCON = SUM(CASE WHEN [BLDGSCHEME] <> ''RMS IND'' AND [BLDGCLASS] = ''0'' THEN 0 ELSE LOCVAL END) 
        ,KnownYB = SUM(CASE WHEN YEAR([YEARBUILT]) = 9999 THEN 0 ELSE LOCVAL END) 
        ,KnownHGT = SUM(CASE WHEN [NUMSTORIES] = 0 THEN 0 ELSE LOCVAL END) 
        ,KnownOCC = SUM(CASE WHEN [OCCTYPE] = ''0'' THEN 0 ELSE LOCVAL END) 
    FROM #temp_locs 
    GROUP BY ACCGRPID, REGION 
) x 
INNER JOIN #temp_polelbyregion y ON x.REGION = y.REGION AND x.ACCGRPID = y.ACCGRPID
';

注意：动态SQL里的单引号需要转义（用两个单引号''）。

方案3：提前创建包含所有列的临时表 #

如果可以的话，一开始创建#temp_polelbyregion时就包含所有需要的列，避免后续修改表结构：

CREATE TABLE #temp_polelbyregion (
    -- 原有列定义...
    REGION varchar(100),
    Coordinate float null,
    Street float null,
    Block float null,
    Postcode float null,
    City float null,
    County float null,
    Cresta float null,
    State float null,
    Country float null,
    None float null,
    KnownCON float null,
    KnownYB float null,
    KnownHGT float null,
    KnownOCC float null
)

-- 后续直接执行UPDATE即可，无需ALTER TABLE

总结 #

最推荐方案1，用GO分隔批处理，简单直观，符合常规SQL编写习惯；动态SQL适合复杂场景；提前创建表则能避免ALTER的开销，效率更高。

内容的提问来源于stack exchange，提问作者Giacomo