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
May 25, 2021 at 3:34 pm
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
May 25, 2021 at 3:58 pm
Yes indeed, that should work.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 25, 2021 at 4:17 pm
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
May 25, 2021 at 6:22 pm
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
Change is inevitable... Change for the better is not.
May 25, 2021 at 7:54 pm
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
May 25, 2021 at 8:26 pm
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 25, 2021 at 8:28 pm
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 26, 2021 at 12:40 pm
Thanks! This appears to be running at least 50% faster than the MERGE
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply