Sql-server – Merge with multiple records at once

sql serversql-server-2016t-sql

There is a piece of code, from a previous dev, that is doing a merge statement with variables, and processing one record at time.

I'm sure the merge can deal with multiple records at one.

create table dbo.Staging
(
    transactionID bigint not null primary key nonclustered,
    transactionDate date not null,
    productId int not null,
    orderId int not null,
    qty int not null,
    unitPrice decimal(8,2) not null
)

create table dbo.Transactions
(
    transactionID bigint not null primary key clustered,
    transactionDate date not null,
    productID int not null,
    ReferenceOrderID  int not null,
    Qty int not null,
    ActualCost decimal(8,2) not null
)
insert into dbo.Staging(transactionID,transactionDate,productId,orderId,qty,unitPrice)
values(1001,'20210101',45889,1501,5,105),
      (1002,'20210102',45892,1503,2,8),
      (1003,'20210103',45555,1555,5,5),
      (1004,'20210104',44444,1444,4,4)

insert into dbo.Transactions(transactionID,transactionDate,productId,ReferenceOrderID,qty,ActualCost)
values
      (1003,'20210101',41111,1111,1,1),
      (1004,'20210101',41111,1111,1,1)

The code is much longer, but the here is the code :

declare 
    @bi_transactionID bigint ,
    @bi_transactionID_init bigint =0,
    @d_transactionDate date,
    @i_productId int,
    @i_orderId int,
    @i_qty int ,
    @dm_unitPrice decimal(8,2)


SELECT @bi_transactionID = MIN(s.transactionID)
FROM dbo.Staging as s
WHERE s.transactionID > @bi_transactionID_init

WHILE @bi_transactionID is not null
BEGIN
        SELECT 
            @bi_transactionID= s.transactionID,
            @d_transactionDate=s.transactionDate ,
            @i_productId =s.productId,
            @i_orderId =s.orderId,
            @i_qty = s.qty,
            @dm_unitPrice = s.unitPrice
        FROM dbo.Staging as s
        WHERE
            s.transactionID = @bi_transactionID

        MERGE dbo.Transactions AS T
        USING (SELECT s.transactionID FROM dbo.Staging as s WHERE s.transactionID = @bi_transactionID) AS s
        ON (t.transactionID = s.transactionID)
        WHEN MATCHED THEN
        UPDATE  SET 
                transactionDate = @d_transactionDate,
                productId = @i_productId,
                ReferenceOrderID=@i_orderId,
                qty = @i_qty,
                ActualCost = @dm_unitPrice        
    
        WHEN NOT MATCHED THEN
        INSERT (transactionID,transactionDate,productId,ReferenceOrderID,qty,ActualCost)     
        VALUES (@bi_transactionID, @d_transactionDate,@i_productId,@i_orderId,@i_qty,@dm_unitPrice);

        SET @bi_transactionID_init = @bi_transactionID

        
        SELECT @bi_transactionID = MIN(s.transactionID)
        FROM dbo.Staging as s
        WHERE s.transactionID > @bi_transactionID_init
        
END

I believe it can be done in one statement, with merge with out the while and varibles.
Can this be done ?

Best Answer

Is this the right ? from my side, the results are the same...

MERGE dbo.Transactions AS T
        USING (SELECT s.transactionID,s.transactionDate,s.productID,s.orderID,s.qty,s.unitPrice FROM dbo.Staging as s) AS s
        ON (t.transactionID = s.transactionID)
        WHEN MATCHED THEN
        UPDATE  SET 
                transactionDate = s.transactionDate,
                productId = s.productId,
                ReferenceOrderID=s.orderId,
                qty = s.qty,
                ActualCost = s.unitPrice        
    
        WHEN NOT MATCHED THEN
        INSERT (transactionID,transactionDate,productId,ReferenceOrderID,qty,ActualCost)     
        VALUES (s.transactionID, s.transactionDate,s.productId,s.orderId,s.qty,s.unitPrice);
Related Question