SQL Syntax – Section in MERGE Statement

It’s been a while since I’ve worked in a T-SQL environment. I previously worked for Google, and, of course, they had their own special flavor of SQL. Now that I’m back, I’m reading back up on my T-SQL features.

I understand the MERGE statement (at least, I think I do!), but I was looking at examples, and I saw one that had some syntax in it that I’m not familiar with:

From MSDocs:

CREATE PROCEDURE Production.usp_UpdateInventory  
    @OrderDate datetime  
AS  
MERGE Production.ProductInventory AS target  
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod  
    JOIN Sales.SalesOrderHeader AS soh  
    ON sod.SalesOrderID = soh.SalesOrderID  
    AND soh.OrderDate = @OrderDate  
    GROUP BY ProductID) AS source (ProductID, OrderQty)  
ON (target.ProductID = source.ProductID)  
WHEN MATCHED AND target.Quantity - source.OrderQty <= 0  
    THEN DELETE  
WHEN MATCHED
    THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,
                    target.ModifiedDate = GETDATE()  
OUTPUT $action, Inserted.ProductID, Inserted.Quantity,
    Inserted.ModifiedDate, Deleted.ProductID,  
    Deleted.Quantity, Deleted.ModifiedDate;  
GO  
  
EXECUTE Production.usp_UpdateInventory '20030501'  

I’m most interested in the following line: GROUP BY ProductID) AS source (ProductID, OrderQty)

What’s going on in those second parentheses? It looked like column aliasing to me, but it seems to not work in the contexts I would expect it to work in (SQL Server 2019).

Answer

I also never did this but just tried assuming this is just about providing alias to the USING clause and its columns which further will be used while evaluating MATCHED and NOT MATCHED conditions.

Lets say you have ,

MERGE Production.ProductInventory AS target  
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod  
         JOIN Sales.SalesOrderHeader AS soh  
           ON sod.SalesOrderID = soh.SalesOrderID  
          AND soh.OrderDate = @OrderDate  
       GROUP BY ProductID) AS source (ProductID, OrderQty)
........
........

Here source is alias to the USING clause and ProductID is alias to the column ProductID mentioned in the select statement of USING clause and similarly OrderQty is alias to the column SUM(OrderQty).

This can also be written as

MERGE Production.ProductInventory AS target  
    USING (SELECT ProductID as ProductID, SUM(OrderQty) as OrderQty 
             FROM Sales.SalesOrderDetail AS sod  
             JOIN Sales.SalesOrderHeader AS soh  
               ON sod.SalesOrderID = soh.SalesOrderID  
              AND soh.OrderDate = @OrderDate  
           GROUP BY ProductID) AS source
    ........
    ........

As per my observation if we give the alias names at end as in your case AS source (ProductID, OrderQty) the columns present within ( ) are assigned aliases sequentially to the columns mentioned in select clause.

Not providing an alias to any column which is using some kind of function will lead to error also.

I have played around with db<>fiddle which you can take a look also.

P.S. This kind of syntax we also use with CTE also which you might have come across