September 24, 2021 at 7:06 pm
I have a merge stored procedure that may be conflicting with the structure of my table. What is happening is the INSERT part of the merge is not happening when it encounters a new record in the source table:
--TABLE KEY definition very very large number of fields, let me know if you want them all... .lol:
PRIMARY KEY CLUSTERED
(
[CALL_ID] ASC,
[DATE] ASC,
[TIME] ASC,
[CALL_TYPE] ASC,
[DOMAIN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
--MERGE structure:
MERGE a2wh.dbo.CallLogCommon AS TARGET
USING F905.dbo.stgCallLogCommon 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
TARGET.[DATE] = SOURCE.[DATE],
TARGET.[TIME] = SOURCE.[TIME],
September 24, 2021 at 10:05 pm
Well, I don't see a WHEN NOT MATCHED section, so how can you expect it to insert new rows?
https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15
September 27, 2021 at 1:01 pm
Here is my complete code for the Merge:
USE [F905]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CLComToA2WH]
AS
BEGIN
SET NOCOUNT ON
UPDATE F905.dbo.stgCallLogCommon SET DOMAIN = '05';
MERGE a2wh.dbo.CallLogCommon AS TARGET
USING F905.dbo.stgCallLogCommon 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
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.[CALLS_COMPLETED_IN_IVR] = SOURCE.[CALLS_COMPLETED_IN_IVR],
TARGET.[CAMPAIGN] = SOURCE.[CAMPAIGN],
TARGET.[LIST_NAME] = SOURCE.[LIST_NAME],
TARGET.[CAMPAIGN_TYPE] = SOURCE.[CAMPAIGN_TYPE],
TARGET.[SKILL] = SOURCE.[SKILL]
WHEN NOT MATCHED BY TARGET THEN
INSERT
(
[CALL_ID],
[DATE],
[TIME],
[lead_id],
[DOMAIN],
[SESSION_ID],
[CALL_TYPE],
[CALLS],
[CALLS_COMPLETED_IN_IVR],
[CAMPAIGN],
[LIST_NAME],
[CAMPAIGN_TYPE],
[SKILL]
)
VALUES
(
SOURCE.[CALL_ID],
SOURCE.[DATE],
SOURCE.[TIME],
SOURCE.[lead_id],
SOURCE.[DOMAIN],
SOURCE.[SESSION_ID],
SOURCE.[CALL_TYPE],
SOURCE.[CALLS],
SOURCE.[CALLS_COMPLETED_IN_IVR],
SOURCE.[CAMPAIGN],
SOURCE.[LIST_NAME],
SOURCE.[CAMPAIGN_TYPE],
SOURCE.[SKILL]
);
TRUNCATE TABLE [F905].[dbo].[stgCallLogCommon]
END
September 30, 2021 at 4:59 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply