CODEWITHSUNDEEP
sql-servert-sqlsql-insert
j.hull
j.hull

Reputation: 440

This SQL INSERT INTO does not work with a UNION

I have been at this for some time and am frustrated. Below is the complete SQL statement I am trying to execute. Simply put, create a temp table from a UNION Select(s) statements.

I can run the SELECTs with the UNION just fine. I can run the separate SELECTS just fine. I can run separate INSERT INTOs just fine. The moment I add an INSERT INTO SELECT * FROM (...) at the top and close it at the bottom I get a syntax error:

Msg 102, Level 15, State 1, Line 75 Incorrect syntax near ')'.

I was hoping this would be simple, but I tried everything I know to get this to work, and cannot. is there something, perhaps with the UNION that creates the syntax error?

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))
 )

Upvotes: 0

Views: 274

Answers (2)

Pawan Kumar
Pawan Kumar

Reputation: 2011

Please try this. Put alias after last bracket.

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))
)c

Upvotes: 1

SQL_M
SQL_M

Reputation: 2475

Place the bracket after the second select statement. That should do it.

 INSERT INTO TableA
(
 SELECT A, B, C
 FROM TableB
  UNION
 SELECT D, E, F
 FROM TableC
 )

Upvotes: 0

Related Questions