Home Blog SQL Merging Records in SQL Table
Merging Records in SQL Table
SQL
Written by Dicky   
Friday, 15 April 2016 11:00
AddThis Social Bookmark Button

One of the cool SQL trick to update/delete/insert record is by using the MERGE function in SQL.

 

So instead having separate statement to check for record existence, delete, iterate to update them or insert them if not exists, we can combine all 3 components into a single statement. Here's an example:

 DECLARE @productItemId NVARCHAR(50) = '123'
 DECLARE @timestamp DATETIME = GETUTCDATE()
 
 BEGIN
 MERGE dbo.ProductTable AS TARGET
 USING (SELECT * FROM SomeTable WHERE ProductItemId=@productItemId) AS SOURCE
 ON TARGET.ProductItemId = @productItemId AND TARGET.RuleId = SOURCE.RuleId
 WHEN MATCHED THEN
 UPDATE SET IsRuleEnabled = SOURCE.IsRuleEnabled, IsValid = SOURCE.IsValid, IsArchived = SOURCE.IsArchived, TotalScore = SOURCE.TotalScore, DateModified = @timestamp
 WHEN NOT MATCHED BY TARGET THEN
 INSERT (ProductItemId, RuleId, IsRuleEnabled, IsValid, IsArchived, ProductItemPhaseId, TotalScore, DateCreated, DateModified)
 VALUES(SOURCE.ProductItemId, SOURCE.RuleId, SOURCE.IsRuleEnabled, SOURCE.IsValid, SOURCE.IsArchived, SOURCE.ProductItemPhaseId, SOURCE.TotalScore, @timestamp, @timestamp)
 WHEN NOT MATCHED BY SOURCE AND TARGET.ProductItemId = @productItemId THEN
 DELETE;

 END

I used them quite often throughout my SQL project. I hope this method could simplify your stored procedure.