MERGE statement with MATCH based on multiple fields

Question

MERGE statement with MATCH based on multiple fields

SSCarpal Tunnel

Points: 4294

May 25, 2021 at 3:14 pm

I have a MERGE stored procedure that I need to modify to check for the MATCH based on five fields below... Is this possible? Below I have it simply checking the Call_ID field:



CREATE PROCEDURE [dbo].[ComToA2WH]

AS
BEGIN

MERGE a2wh.dbo.CallLogCommon_dbc AS TARGET

USING F905.dbo.stgCallLogCommon_dbc AS SOURCE

ON (TARGET.[CALL_ID] = SOURCE.[CALL_ID])

WHEN MATCHED THEN UPDATE SET 

TARGET.[DATE] = SOURCE.[DATE],
TARGET.[TIME] = SOURCE.[TIME],
TARGET.[lead_id] = SOURCE.[lead_id],
TARGET.[DOMAIN] = SOURCE.[DOMAIN], 
TARGET.[SESSION_ID] = SOURCE.[SESSION_ID],
TARGET.[CALL_TYPE] = SOURCE.[CALL_TYPE],
TARGET.[CALLS] = SOURCE.[CALLS],
TARGET.[CAMPAIGN] = SOURCE.[CAMPAIGN],
TARGET.[LIST_NAME] = SOURCE.[LIST_NAME],
TARGET.[CAMPAIGN_TYPE] = SOURCE.[CAMPAIGN_TYPE],
TARGET.[SKILL] = SOURCE.[SKILL],
TARGET.[CUSTOMER_NAME] = SOURCE.[CUSTOMER_NAME],
TARGET.[DISPOSITION] = SOURCE.[DISPOSITION]

WHEN NOT MATCHED BY TARGET THEN INSERT 

(
[CALL_ID],
[DATE],
[TIME],
[lead_id],
[DOMAIN], 
[SESSION_ID],
[CALL_TYPE],
[CALLS],
[CAMPAIGN],
[LIST_NAME],
[CAMPAIGN_TYPE],
[SKILL],
[CUSTOMER_NAME],
[DISPOSITION]

)

VALUES
(SOURCE.[CALL_ID],
SOURCE.[DATE],
SOURCE.[TIME],
SOURCE.[lead_id],
SOURCE.[DOMAIN], 
SOURCE.[SESSION_ID],
SOURCE.[CALL_TYPE],
SOURCE.[CALLS],
SOURCE.[CAMPAIGN],
SOURCE.[LIST_NAME],
SOURCE.[CAMPAIGN_TYPE],
SOURCE.[SKILL],
SOURCE.[CUSTOMER_NAME],
SOURCE.[DISPOSITION]);

END

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 1

I'm hoping this will work:

MERGE a2wh.dbo.CallLogCommon_dbc AS TARGET

USING F905.dbo.stgCallLogCommon_dbc AS SOURCE

ON TARGET.[CALL_ID] = SOURCE.[CALL_ID] AND 
TARGET.[DATE] = SOURCE.[DATE] AND
TARGET.[DOMAIN] = SOURCE.[DOMAIN] AND
TARGET.[CALL_TYPE] = SOURCE.[CALL_TYPE] AND
TARGET.[TIME] = SOURCE.[TIME]

WHEN MATCHED

THEN UPDATE SET

Phil Parkin SSC Guru Points: 246747 More actions · Answer 2

Yes indeed, that should work.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Jonathan AC Roberts SSCoach Points: 18279 More actions · Answer 3

You will get better performance if you write 2 statements, an update followed by an insert (where not exists)

Performance Tip: The conditional behavior described for the MERGE statement works best when the two tables have a complex mixture of matching characteristics. For example, inserting a row if it doesn't exist, or updating a row if it matches. When simply updating one table based on the rows of another table, improve the performance and scalability with basic INSERT, UPDATE, and DELETE statements.

https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15

Jeff Moden SSC Guru Points: 1003935 More actions · Answer 4

I'll second what Jonathan stated about performance. I also have a really bad aftertaste about MERGE and all the nightmares it caused when it first came out. They may have fixed it good but the damage has been done for my trust (lack of, actually) for it and it's really no more difficult to write a good 2 or 3 part "upsert" and so I don't actually even bother with MERGE.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 5

Like this? Note the syntax problems:

BEGIN

INSERT a2wh.dbo.CallLogCommon_dbc D

(D.[CALL_ID],
D.[DATE],
D.[TIME],
D.[lead_id],
D.[DOMAIN], 
D.[SESSION_ID],
D.[CALL_TYPE],
D.[CALLS],
D.[CAMPAIGN],
D.[LIST_NAME],
D.[CAMPAIGN_TYPE],
D.[SKILL],
D.[CUSTOMER_NAME],
D.[DISPOSITION]) --DOES NOT LIKE THE ")" SYNTAX

SELECT

(S.[CALL_ID], --DOES NOT LIKE THE COMMA SYNTAX
S.[DATE],
S.[TIME],
S.[lead_id],
S.[DOMAIN], 
S.[SESSION_ID],
S.[CALL_TYPE],
S.[CALLS],
S.[CAMPAIGN],
S.[LIST_NAME],
S.[CAMPAIGN_TYPE],
S.[SKILL],
S.[CUSTOMER_NAME],
S.[DISPOSITION])

FROM F905.dbo.stgCallLogCommon_dbc S

WHERE NOT EXISTS (SELECT 
D.[CALL_ID],
D.[DATE], 
D.[TIME], 
D.[CALL_TYPE], 
D.[DOMAIN] 

FROM a2wh.dbo.CallLogCommon_dbc D

WHERE 
D.[CALL_ID] = S.[CALL_ID] AND
D.[DATE] = S.[DATE] AND 
D.[TIME] = S.[TIME] AND 
D.[CALL_TYPE] = S.[CALL_TYPE] AND 
D.[DOMAIN] = S.[DOMAIN]);

END

Phil Parkin SSC Guru Points: 246747 More actions · Answer 6

Nearly! More like this, I think:

INSERT a2wh.dbo.CallLogCommon_dbc
(
    CALL_ID
   ,DATE
   ,TIME
   ,lead_id
   ,DOMAIN
   ,SESSION_ID
   ,CALL_TYPE
   ,CALLS
   ,CAMPAIGN
   ,LIST_NAME
   ,CAMPAIGN_TYPE
   ,SKILL
   ,CUSTOMER_NAME
   ,DISPOSITION
)
SELECT S.CALL_ID
      ,S.DATE
      ,S.TIME
      ,S.lead_id
      ,S.DOMAIN
      ,S.SESSION_ID
      ,S.CALL_TYPE
      ,S.CALLS
      ,S.CAMPAIGN
      ,S.LIST_NAME
      ,S.CAMPAIGN_TYPE
      ,S.SKILL
      ,S.CUSTOMER_NAME
      ,S.DISPOSITION
FROM F905.dbo.stgCallLogCommon_dbc S
WHERE NOT EXISTS
(
    SELECT 1
    FROM a2wh.dbo.CallLogCommon_dbc D
    WHERE D.CALL_ID = S.CALL_ID
          AND D.DATE = S.DATE
          AND D.TIME = S.TIME
          AND D.CALL_TYPE = S.CALL_TYPE
          AND D.DOMAIN = S.DOMAIN
);

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Phil Parkin SSC Guru Points: 246747 More actions · Answer 7

Your BEGIN ... END surround a single statement and should therefore not be required, unless you put them there for clarity.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 8

Thanks! This appears to be running at least 50% faster than the MERGE