SQL Server: update rows that take part in grouping

I’m using SQL Server 2017 (v14.0).

I have two tables with one-to-many relationship. I need to group the rows in the “Orders” table and by this info create the row in the “Transactions” table, then I need set the relationship – for a created transaction I need set the TransactionId to related order’s rows in one query/transaction flow to keep the consistency.

I correctly insert the new row by grouped data, but can’t update the related rows in “Orders” table to set the relationship (TransactionId) for related “Transactions”.

Can you, please, help with composing the query statement or get the clue to move in the right direction?

CREATE TABLE [dbo].[Orders]
(
    [OrderId] INT NOT NULL,
    [TransactionId] INT NULL,
    [OrderVolume] DECIMAL(18, 8) NOT NULL,
    [OrderCurrencyId] INT NOT NULL,
)

CREATE TABLE [dbo].[Transactions]
(
    [TransactionId] INT NULL,
    [Volume] DECIMAL(18, 8) NOT NULL,
)

INSERT INTO Transactions (Volume)
OUTPUT INSERTED.[TransactionId] --also need to update the rows in "Orders" that take a part in grouping to set the relationship
SELECT 
    SUM(OrderVolume) AS OrderVolume,    
FROM Orders
GROUP BY Orders.OrderCurrencyId

Answer

The problem with the OUTPUT clause in an INSERT statement is that it doesn’t allow you to select any field from the source table.

You can achieve this using MERGE statement instead:

DECLARE @t TABLE([TransactionId] INT, [OrderCurrencyId] INT)


MERGE Transactions  trgt
USING 
(  
   SELECT 
      SUM(OrderVolume) AS OrderVolume
      , Orders.OrderCurrencyId AS OrderCurrencyId
   FROM 
      Orders
   GROUP BY 
      Orders.OrderCurrencyId

) AS src ON (1=0)
WHEN NOT MATCHED THEN 
 INSERT ( [Volume] ) VALUES (src.OrderVolume)
OUTPUT [inserted].[TransactionId], src.[OrderCurrencyId]
INTO @t;


UPDATE Orders 
SET TransactionId = t.TransactionId
FROM Orders
JOIN @t t ON Orders.OrderCurrencyId = t.OrderCurrencyId

Demo here