Insert, Update and Delete Using Merge Statement in SQL With Update to Third Table.

This Example will Guide you to update a bulk data of a table.
In Below example Master ID (@JournalVoucherID) is used to recognize the data set and also to Compare the Source and Target Data.

Working Loogic:

1. Common Dataset gets UPDATED as in case of WHen Matched.
2. New DataRow from Source is INSERTED in Target as in case of WHEN NOT MATCHED By             Target.
3. Extra data in Target with same master ID is DELETEDE as in case of WHEN NOT MATCHED By Source and Target.JournalVoucherID=@JournalVoucherid.
4. And at the end DELETED Data needs to update some more Table hence we use
    output deleted.ChequeNo into @Cheque.Where @Cheque is another table variable to maintain a      third table.

GO
alter proc [dbo].[prInsert_JournalDetailsBulk]
@JournalDetails JournalDetails ReadOnly
,@JournalVoucherid int
AS
BEGIN
Declare @Cheque Table(id int )

MERGE INTO 

             tbl_journaldetails  as Target 

Using   @JournalDetails as Source
on (
              Target.JournalVoucherID =@JournalVoucherid
     )


WHen Matched  and Target.[JournadetailID]=Source.[JournadetailID]
THEN 
UPDATE SET 
Target.[LedgerMasterID]=Source.[LedgerMasterID],
Target.[ProjectMasterID]=Source.[ProjectMasterID],
Target.[SNo]=Source.[SNo],
Target.[SubLedgerID]=Source.[SubLedgerID],
Target.[Narration]=Source.[Narration],
Target.[DrAmount]=Source.[DrAmount],
Target.[CrAmount]=Source.[CrAmount],
Target.[TDSPan]=Source.[TDSPan],
Target.[TDSName]=Source.[TDSName],
Target.[TDSAmount]=Source.[TDSAmount],
Target.[TDSTDS]=Source.[TDSTDS],
Target.[TDSDate]=Source.[TDSDate],
Target.[TDSType]=Source.[TDSType],
Target.[Remarks]=Source.[Remarks],
Target.[SourceID]=Source.[SourceID],
Target.[ProgramID]=Source.[ProgramID],
Target.[BudgetHeadMasterID]=Source.[BudgetHeadMasterID],
Target.[BudgetSubHeadMasterID]=Source.[BudgetSubHeadMasterID],
Target.[ChequeNo]=Source.[ChequeNo],
Target.[ChequeDate]=Source.[ChequeDate],
Target.[ChequeAmount]=Source.[ChequeAmount]
WHEN NOT MATCHED By Target 
          THEN 

 INSERT ([LedgerMasterID]
           ,[ProjectMasterID]
           ,[JournalVoucherID]
           ,[SNo]
           ,[SubLedgerID]
           ,[Narration]
           ,[DrAmount]
           ,[CrAmount]
           ,[TDSPan]
           ,[TDSName]
           ,[TDSAmount]
           ,[TDSTDS]
           ,[TDSDate]
           ,[TDSType]
           ,[Remarks]
  ,[SourceID]
  ,[ProgramID]
  ,[BudgetHeadMasterID] 
  ,[BudgetSubHeadMasterID] 
  ,[ChequeNo] 
  ,[ChequeDate] 
  ,[ChequeAmount]  
  )
     VALUES
          ( 
   Source.[LedgerMasterID]
           ,Source.[ProjectMasterID]
           ,@JournalVoucherid
           ,Source.[SNo]
           ,Source.[SubLedgerID]
           ,Source.[Narration]
           ,Source.[DrAmount]
           ,Source.[CrAmount]
           ,Source.[TDSPan]
           ,Source.[TDSName]
           ,Source.[TDSAmount]
           ,Source.[TDSTDS]
           ,Source.[TDSDate]
           ,Source.[TDSType]
           ,Source.[Remarks]
  ,Source.[SourceID]
  ,Source.[ProgramID]
  ,Source.[BudgetHeadMasterID] 
  ,Source.[BudgetSubHeadMasterID] 
  ,Source.[ChequeNo] 
  ,Source.[ChequeDate] 
  ,Source.[ChequeAmount]  
  )



WHEN NOT MATCHED By Source and Target.JournalVoucherID=@JournalVoucherid

then delete

output deleted.ChequeNo into @Cheque

;



 update [tbl_ChequeBookEntry]set [Status]='false' where [ID] in (select ID from  @Cheque)

end