views:

113

answers:

2

I'm new to T-SQL command MERGE so I found a place in my SQL logic where I can use it and want to test it but can't figure out how exatcly should I use it:

IF (EXISTS (SELECT 1 FROM commissions_history WHERE request = @requestID))
    UPDATE commissions_history
    SET amount = @amount
    WHERE request = @requestID
ELSE
    INSERT INTO commissions_history (amount) VALUES @amount)

Plase suggest the proper usage. Thanks!

+1  A: 

I hope it will work

MERGE commissions_history AS target
USING (SELECT request FROM  commissions_history WHERE request = @requestID) AS source (request)
ON (target.request = source.request)
WHEN MATCHED THEN 
    UPDATE SET amount = @amount
WHEN NOT MATCHED BY SOURCE    
    INSERT (request, amount)
    VALUES (@requestID,  @amount)
a1ex07
The insert part of that won't work, because your source select will return an empty recordset, which it will then try to merge
Tom H.
In the update, source.amount is from the taable commisions_history, but needs to be from the variable @Amount.
Shannon Severance
@Tom H, @Shannon Severance: Thanks for comments. I've changed it. Anyway, Tom H's is more clear.
a1ex07
+6  A: 

Did you look in the help? Here's a simple example:

MERGE dbo.commissions_history AS target
USING (SELECT @amount, @requestID) AS source (amount, request)
ON (target.request = source.request)
WHEN MATCHED THEN
    UPDATE SET amount = source.amount
WHEN NOT MATCHED THEN
    INSERT (request, amount)
    VALUES (source.request, source.amount);
Tom H.