Kumar AU

Kumar AU

  • 1.1k
  • 309
  • 61.9k

Using Merge statement in SQL how to Update or INSERT

Aug 23 2023 8:25 PM

I want to write stored proc , and input to the stored proc will be just a Table type. Basically, in the table type there will be set of records and If incorrect number not matched and correct number matched then Update or else INSERT

This is what I have tried: But it always inserts the records instead of Update even though it belongs to same date range and CorrectPolicyNumber matches which is mentioned in my Merge statement.

CREATE PROCEDURE [dbo].[AddOrUpdateAccountRecords]    
    @AccountDataTableType  dbo.bPAccountType READONLY     
AS  
BEGIN  
  SET NOCOUNT ON; 
  
  
MERGE [BillAccount] AS target
USING @AccountDataTableType AS source
    ON target.CorrectAccountReference = source.CorrectPolicyNumber
    AND Convert(date, target.CreatedDate) < Convert(date, getdate()-1)
WHEN MATCHED AND target.IncorrectAccountReference <> source.IncorrectPolicyNumber THEN 
    UPDATE SET target.IncorrectAccountReference = source.IncorrectPolicyNumber,target.CreatedDate = getdate()
       
WHEN NOT MATCHED BY TARGET THEN
    INSERT (IncorrectAccountReference, CorrectAccountReference, CreatedDate)
    VALUES (source.IncorrectPolicyNumber, source.CorrectPolicyNumber, Getdate());

END

Below is the Table type :-

CREATE TYPE dbo.bPAccountType AS TABLE(
    [CorrectPolicyNumber]           VARCHAR(20)  NOT NULL,
    [IncorrectPolicyNumber]      VARCHAR(20)     NOT NULL
);

Sample Data BillAccount Table:- in the below picture , you can notice that row 12 and 14 matches the condition, it should update instead of inserting new row 14, Can you please help me what I am doing wrong in my query

 


Answers (3)