CODEWITHSUNDEEP
sqlsql-server
RealityDysfunction
RealityDysfunction

Reputation: 2639

Inefficient Merge with 1 row

This merge statement seems inefficient to me, to insert/update I create a whole new table, then insert into that table then merge, is there a better way?

DECLARE @dbEntry TABLE(
Email varchar(100)
,DateRedeemed datetime
)
INSERT INTO @dbEntry VALUES (@Email, @DateRd)

MERGE [dbo].[Redeemers] AS dbTarget
USING
(
SELECT * FROM @dbEntry
)
AS dbSource
ON
(
dbSource.Email = dbTarget.Email
)

WHEN MATCHED
....

WHEN NOT MATCHED 
...

PS: This query is simplified for display purposes, there are a lot more columns and a few more operations before and after.

Upvotes: 2

Views: 70

Answers (2)

D Stanley
D Stanley

Reputation: 152626

MERGE is designed for processing multiple rows, where the INSERT/UPDATE decision needs to be made for each row. It isn't very effective with just one row, because you can just do this:

IF EXISTS(SELECT NULL FROM Redeemers WHERE Email = @Email)
    UPDATE Redeemers SET DateRedeemed = @DateRd WHERE Email = @Email
ELSE
    INSERT INTO Redeemers (Email, DateRedeemed) VALUES (@Email, @DateRd)
ENDIF

Upvotes: 1

Ndech
Ndech

Reputation: 965

Why not just do that ?

MERGE [dbo].[Redeemers] AS dbTarget
USING
(
SELECT @Email as Email, @DateRd as DateRd
)
AS dbSource
ON
(
dbSource.Email = dbTarget.Email
)

WHEN MATCHED
....

WHEN NOT MATCHED 
...

Upvotes: 2

Related Questions