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

Update from Another table or select query in SQL

In case when we need to Update a table from another table or select query we can use this syntax.

For update from another Table. 

Syntax:

UPDATE
    Table
SET
    Table.col1 = other_table.col1,
    Table.col2 = other_table.col2
FROM
    Table
INNER JOIN
    other_table
ON
    Table.id = other_table.id

Update From Select Query. 

example:

update 

    tbl_JournalMaster 

set 

    Remarks=dbj.pname 

from 

    tbl_JournalMaster 

inner join 

(
select 

    distinct DJ.vno,

            (select 

                 top(1) pname 

             from 

                 DBASEJournal 

             where 

                 vno=dj.vno) as pname 

             from 

                 DBASEJournal DJ

 ) dbj 

on 

  dbj.VNO=tbl_JournalMaster.JVNo  ;

Feel Free To Contact For any Comments

Gautam Kumar Gupta

Go To Home Page