SQL

sql merge statement

MERGE LoginTypes T
        USING (SELECT 'System' as Description) S
        ON(S.Description = T.Description)
WHEN NOT MATCHED BY TARGET
    THEN INSERT(Description, CreatedTimestamp, LastUpdatedTimestamp)
VALUES('System', getdate(), getdate());

merge command in sql

USE SqlShackMergeDemo
GO
    
MERGE TargetProducts AS Target
USING SourceProducts	AS Source
ON Source.ProductID = Target.ProductID
    
-- For Inserts
WHEN NOT MATCHED BY Target THEN
    INSERT (ProductID,ProductName, Price) 
    VALUES (Source.ProductID,Source.ProductName, Source.Price)
    
-- For Updates
WHEN MATCHED THEN UPDATE SET
    Target.ProductName	= Source.ProductName,
    Target.Price		= Source.Price
    
-- For Deletes
WHEN NOT MATCHED BY Source THEN
    DELETE;

merge in sql

/* Selecting the Target and the Source */
MERGE PRODUCT_LIST AS TARGET
    USING UPDATE_LIST AS SOURCE
 
    /* 1. Performing the UPDATE operation */
 
    /* If the P_ID is same,
       check for change in P_NAME or P_PRICE */
    ON (TARGET.P_ID = SOURCE.P_ID)
    WHEN MATCHED
         AND TARGET.P_NAME <> SOURCE.P_NAME
         OR TARGET.P_PRICE <> SOURCE.P_PRICE
 
    /* Update the records in TARGET */
    THEN UPDATE
         SET TARGET.P_NAME = SOURCE.P_NAME,
         TARGET.P_PRICE = SOURCE.P_PRICE
      
    /* 2. Performing the INSERT operation */
 
    /* When no records are matched with TARGET table
       Then insert the records in the target table */
    WHEN NOT MATCHED BY TARGET
    THEN INSERT (P_ID, P_NAME, P_PRICE)         
         VALUES (SOURCE.P_ID, SOURCE.P_NAME, SOURCE.P_PRICE)
 
    /* 3. Performing the DELETE operation */
 
    /* When no records are matched with SOURCE table
       Then delete the records from the target table */
    WHEN NOT MATCHED BY SOURCE
    THEN DELETE
 
/* END OF MERGE */