MERGE statement with MATCH based on multiple fields

Question

MERGE statement with MATCH based on multiple fields

SSCarpal Tunnel

Points: 4298

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: 4298 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: 246988 More actions · Answer 2

Yes indeed, that should work.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jonathan AC Roberts SSCoach Points: 18363 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: 1004414 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: 4298 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: 246988 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
);

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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

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

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 8

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