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

Using Merge in SQL Server 2008 R2

  --To Merge Data of different tables we can use merge statement in sql which first checks the content of both the tables and adds the data in the Target table which are missing from Soucre Table.

Syntax:

MERGE INTO [TARGETTABLE] AS Target
    USING [SOURCETABLE] AS SOURCE
    ON
        --unique key filtering condition
        Target.COLUMN1=SOURCE.COLUMN1 AND
        Target.COLUMN2=SOURCE.COLUMN2 AND
        Target.COLUMN3 =SOURCE.COLUMN3 AND
        Target.COLUMN4 =SOURCE.COLUMN4
     WHEN MATCHED THEN
        --update or delete query
        UPDATE SET
            Target.ColumnA=SOURCE.ColumnA,
            Target.ColumnB=SOURCE.ColumnB,
            Target.ColumnC=SOURCE.ColumnC

    WHEN NOT MATCHED THEN
        --INSERT QUERY
        INSERT (COLUMNA, ColumnB, ColumnC, ColumnD)
        VALUES(
                        SOURCE.ColumnA,
                        SOURCE.ColumnB,
                        SOURCE.ColumnC,
                        SOURCE.ColumnD                    )
    ;