带UNION的INSERT INTO语句报错求助：Msg 102语法错误

你遇到的这个语法错误其实是SQL Server的一个常见规则导致的——所有作为数据源的派生表（也就是你括号里的UNION查询）必须指定别名，这就是你收到Msg 102, Level 15, State 1, Line 75 Incorrect syntax near ')'错误的核心原因。

快速修正方法 #

只需要在闭合括号的末尾添加一个任意的别名（比如t、src这类简单标识即可），不需要复杂命名，只要符合SQL标识符规则就行。

修正后的完整SQL语句 #

INSERT INTO #Tmp6 SELECT * FROM (
 SELECT [ItemStatus] ,[Item_Number] ,[Item_Name] ,[Default_Sales_Name] ,[Special_Order_Item] ,[Returnable] ,[Return_Number_Of_Days] ,[Is_Drop_Ship] ,[Is_JIT] ,[Vendor_Name] ,[Vendor_Number] ,[Default_Sales_Price] ,[Cost_Of_Goods] ,[Purchase_Cost] ,[Additional_Cost1_Text] ,[Additional_Cost1_Value] ,[Additional_Cost2_Text] ,[Additional_Cost2_Value] ,[Additional_Cost3_Text] ,[Additional_Cost3_Value] ,[UPC_Value] ,[Weight] ,[DimWeight] ,[Discontinued_Date] ,[Personalization_Template_Number] ,[Harmonized_Code] ,[Base_Category] ,[Sub_Category] ,[End_Category] ,[RowID]
 FROM [dbo].[RAIProductsStg] p
 WHERE ItemStatus <> 'Discontinued' OR p.Item_Number IN (SELECT i.Item_Number FROM RAIInventoryStg i WHERE p.Item_Number = i.Item_Number AND Quantity > 0)
 UNION
 SELECT [ItemStatus] ,[Item_Number] ,[Item_Name] ,[Default_Sales_Name] ,[Special_Order_Item] ,[Returnable] ,[Return_Number_Of_Days] ,[Is_Drop_Ship] ,[Is_JIT] ,[Vendor_Name] ,[Vendor_Number] ,[Default_Sales_Price] ,[Cost_Of_Goods] ,[Purchase_Cost] ,[Additional_Cost1_Text] ,[Additional_Cost1_Value] ,[Additional_Cost2_Text] ,[Additional_Cost2_Value] ,[Additional_Cost3_Text] ,[Additional_Cost3_Value] ,[UPC_Value] ,[Weight] ,[DimWeight] ,[Discontinued_Date] ,[Personalization_Template_Number] ,[Harmonized_Code] ,[Base_Category] ,[Sub_Category] ,[End_Category] ,[RowID]
 FROM [dbo].[RAIProductsStg] p
 LEFT JOIN RAIParentChildStg pc ON p.Item_Number = pc.ParentItemNumber
 WHERE(LEN(p.Item_Number) = 6 AND p.ItemStatus = 'Discontinued') AND (pc.ChildItemNumber IN (SELECT i.Item_Number FROM RAIInventoryStg i WHERE pc.ChildItemNumber = i.Item_Number AND i.Quantity > 0))
) t -- 这里添加派生表的别名即可

补充说明 #

• 单独执行UNION查询时不需要别名，因为它是直接返回结果；但当它作为FROM子句的数据源时，SQL Server需要用别名来识别这个临时结果集，哪怕后续不会主动使用这个别名。
• 额外建议：如果#Tmp6的列结构和UNION查询的结果不完全匹配（或者未来可能有结构变更），最好显式指定INSERT的列名，比如INSERT INTO #Tmp6 (ItemStatus, Item_Number, ...) SELECT ...，这样代码更健壮，也更容易排查问题。

内容的提问来源于stack exchange，提问作者j.hull