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