Merge Concept In SQL

in this blog we learn how to use merge in SQL for Insert, update & delete Record as per merge conditionally for bult insert, update & delete bulk records From Source Table To Target Table

Merge Concept in sql,

CREATE proc proc_MearginCocept
as
begin
if exists(select 1 from sys.objects where type='u' and name in 
('Tbl_Vendor','dbo.Tbl_Vendor_History','Tbl_VendorProfile'))
begin
DROP Table dbo.Tbl_Vendor;
DROP Table dbo.Tbl_Vendor_History
DROP Table Tbl_VendorProfile
end

CREATE TABLE dbo.Tbl_Vendor
(
    ID  INT IDENTITY(1,1),
    VendorName    VARCHAR(50),
    Mobile   VARCHAR(20),
    Contact  VARCHAR(50)
);
 
CREATE TABLE dbo.Tbl_Vendor_History
(
        ID              INT IDENTITY(1,1),
    VendorID  INT,
    VendorBusinessName    VARCHAR(50),
    VendorTelephone   VARCHAR(20),
    VendorContact  VARCHAR(50),
    VendorActionType  VARCHAR(10),
    TrDateTime   DATETIME DEFAULT GETDATE()
);
CREATE TABLE dbo.Tbl_VendorProfile
    (
      VPBusinessName      VARCHAR(50) NULL ,
      VPTelephone     VARCHAR(20) NULL ,
      VPContact    VARCHAR(50) NULL
    ); 
--GO

Merge dbo.Tbl_Vendor as Target
using dbo.Tbl_Vendor_History as Source
ON (Target.VendorName=Source.VendorBusinessName)
WHen Matched THEN
UPdate  set Target.VendorName=Source.VendorBusinessName
OUTPUT $action, inserted.*, deleted.*;

INSERT INTO dbo.Tbl_Vendor
        ( VendorName ,
          Mobile ,
          Contact )
    OUTPUT  Inserted.ID ,
        Inserted.VendorName ,
        Inserted.Mobile ,
        Inserted.Contact ,
        'INSERT'
    INTO dbo.Tbl_Vendor_History ( VendorID, 
                    VendorBusinessName, 
                    VendorTelephone,
                    VendorContact, 
                    VendorActionType )
    VALUES
        ( 'Purchese Shares from Sbank' ,
          '8777777777' ,
          'Sai Pathrikar' );
--GO

UPDATE dbo.Tbl_Vendor
SET Mobile = '7777777777'
OUTPUT Deleted.ID ,
   Deleted.VendorName ,
   Deleted.Mobile ,
   Deleted.Contact,
   'UPDATE'
INTO dbo.Tbl_Vendor_History(VendorID, 
              VendorBusinessName, 
              VendorTelephone, 
              VendorContact, 
              VendorActionType)
WHERE ID = 1;
 
 
UPDATE dbo.Tbl_Vendor
SET Contact = 'Sai Pravin Pathrikar'
OUTPUT Deleted.ID ,
   Deleted.ID ,
   Deleted.Mobile ,
   Deleted.Contact,
   'UPDATE'
INTO dbo.Tbl_Vendor_History(VendorID, 
              VendorBusinessName, 
              VendorTelephone, 
              VendorContact, 
              VendorActionType)
WHERE ID = 1;
 
UPDATE dbo.Tbl_Vendor
SET VendorName = 'PAthrikar Sai'
OUTPUT Deleted.ID ,
   Deleted.VendorName ,
   Deleted.Mobile ,
   Deleted.Contact,
  'UPDATE'
INTO dbo.Tbl_Vendor_History(VendorID, 
              VendorBusinessName, 
              VendorTelephone, 
              VendorContact, 
              VendorActionType)
WHERE ID = 1;



 
--GO
 
INSERT INTO dbo.Tbl_VendorProfile
        ( VPBusinessName ,
          VPTelephone ,
          VPContact )
    VALUES
        ( 'Sharayu Pathrikar' ,  '9977777777' , 'Swaraj Pathrikar'), 
        ( 'Kartik Ingle', '1122554488', 'Dhanashree ingle');     


		MERGE dbo.Tbl_Vendor AS s
USING dbo.Tbl_VendorProfile AS ss
ON ss.VPBusinessName = s.VendorName
WHEN NOT MATCHED BY TARGET THEN
    INSERT ( VendorName ,
             Mobile ,
             Contact )
    VALUES ( ss.VPBusinessName ,
             ss.VPTelephone ,
             ss.VPContact )
WHEN MATCHED THEN
    UPDATE SET s.VendorName = ss.VPBusinessName ,
                s.Mobile = ss.VPTelephone ,
                s.Contact = ss.VPContact
OUTPUT CASE WHEN $action = 'INSERT' THEN Inserted.ID
            ELSE Deleted.ID
       END ,
    CASE WHEN $action = 'INSERT' THEN Inserted.VendorName
         ELSE Deleted.VendorName
    END ,
    CASE WHEN $action = 'INSERT' THEN Inserted.Mobile
         ELSE Deleted.Mobile
    END ,
    CASE WHEN $action = 'INSERT' THEN Inserted.Contact
         ELSE Deleted.Contact
    END ,
    $action
    INTO dbo.Tbl_Vendor_History ( VendorID, VendorBusinessName, VendorTelephone,
                                VendorContact, VendorActionType )
								OUTPUT $Action, inserted.*,Deleted.*;
								end

Now execute procedure,

EXEC proc_MearginCocept

Output