title: SQL Stored Procedure using Merge (Update/Insert) and Group By
body: <p>I have a Stored Procedure that I need to add Merge functionality to. I need to Update on Matched rfds_processing_id and Insert when Not Matched. The Group By and Where statements are making it tricky. </p>
  
  <p><strong>This is the current, working SP</strong></p>
  
  <pre><code>INSERT INTO Summary_Current(model, type, elevation, rfds_processing_id, qty)
    SELECT model, type, elevation, rfds_processing_id, SUM(qty)
      FROM Current_Antenna C
      WHERE C.rfds_processing_id = @rfds_processing_id 
        AND C.model != '' 
        AND C.type IS NOT NULL 
        AND C.elevation IS NOT NULL
      GROUP BY C.model, C.type, C.elevation, rfds_processing_id
  
  </code></pre>
  
  <p><strong>This is where I am on the Merge</strong>. I'm getting the following error:</p>
  
  <pre><code>*Msg 8155, Level 16, State 2, Procedure CurrentSummary, Line 33 [Batch Start Line 5]
  No column name was specified for column 5 of 'SOURCE'.*
  
  
  MERGE INTO Summary_Current AS TARGET
  USING 
    (
    SELECT model, type, elevation, rfds_processing_id, SUM(qty)
    FROM Current_Antenna C
    WHERE C.rfds_processing_id = @rfds_processing_id AND C.type IS NULL AND C.elevation IS NULL
    GROUP BY C.model, C.type, C.elevation, rfds_processing_id  
    ) AS SOURCE
    ON SOURCE.rfds_processing_id = @rfds_processing_id --AND TARGET.rfds_processing_id = @rfds_processing_id
      WHEN NOT MATCHED THEN
        INSERT (model, type, elevation, rfds_processing_id, qty) 
        VALUES (SOURCE.model, SOURCE.type, SOURCE.elevation, SOURCE.rfds_processing_id, SOURCE.qty)
      WHEN NOT MATCHED BY SOURCE THEN DELETE;
  
  </code></pre>
  
  <p><strong>Updates to Working Merge</strong></p>
  
  <pre><code>ALTER PROCEDURE [dbo].[CurrentSummary]
  (
      @rfds_processing_id uniqueidentifier = '',
  )
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON
  
      -- Insert statements for procedure here
  MERGE Summary_Current AS TARGET
  USING 
    (
    SELECT model, type, elevation, rfds_processing_id, SUM(qty) as total_qty
    FROM Current_Antenna C
    WHERE rfds_processing_id = @rfds_processing_id AND C.model !=''
    GROUP BY C.model, C.type, C.elevation, C.rfds_processing_id  
    ) AS SOURCE
       ON SOURCE.rfds_processing_id = TARGET.rfds_processing_id AND SOURCE.model != ''
        WHEN NOT MATCHED THEN
          INSERT (model, type, elevation, rfds_processing_id, qty) 
          VALUES (SOURCE.model, SOURCE.type, SOURCE.elevation, SOURCE.rfds_processing_id, SOURCE.total_qty) ;
  END
  </code></pre>