SQL Merge update issue

Question

I am trying to increment a project code when I do a SQL merge operation

I have two tables. One has lots of information including customer's and projects. I want to merge the customer name and project name from one table to the other. This article is perfect and showed me how to do what I needed to do

https://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/

However I need to maintain a project number that increments every time a record is added and left alone when you do an edit of customer or project name. If the project is deleted then we carry on from the next available number. I was trying to do it using row number over partition but it didn't give me the correct number of projects.

Using the articles example and to provide a visualisation I would need another column called Type with Food or Drink as the answer and get

Item     Cost Code   Type
Tea      10    1     Drink
Coffee   12    2     Drink
Muffin   11    1     Food
Biscuit  4     2     Food

Answer 1

I will go with the data from the example provided from the link and add a bit more data to be sure im covering all the cases so first lets start with these tables and fill them.

--Create a target table
Declare @Products TABLE 
(
   ProductID INT PRIMARY KEY,
   ProductName VARCHAR(100),
   ProductNumber int,
   ProductType VARCHAR(100),
   Rate MONEY
) 

--Insert records into target table
INSERT INTO @Products
VALUES
   (1, 'Tea',       1,'Drink',10.00),
   (2, 'Coffee',    2,'Drink', 20.00),
   (3, 'BiscuitX1', 1,'Food', 45.00) ,
   (4, 'Muffin',    2,'Food', 30.00),
   (5, 'BiscuitX2', 3,'Food', 40.00),
   (6, 'BiscuitX3', 4,'Food', 45.00),
   (7, 'Donut',     5, 'Food', 30.00),
   (8, 'BiscuitX4', 6,'Food', 40.00),
   (9, 'BiscuitX5', 7,'Food', 45.00)  
--Create source table
Declare @UpdatedProducts TABLE 
(
   ProductID INT PRIMARY KEY,
   ProductName VARCHAR(100),
   ProductNumber int,
   ProductType VARCHAR(100),
   Rate MONEY
) 

--Insert records into source table
INSERT INTO @UpdatedProducts
VALUES
   (1, 'Tea',           0,'Drink', 10.00),
   (2, 'Coffee',        0,'Drink', 25.00),
   (4, 'Muffin',        0,'Food', 35.00),
   (7, 'Donut',         0, 'Food', 30.00),
   (10, 'Pizza',        0,'Food', 60.00),
   (11, 'PizzaLarge',   0,'Food', 80.00)

You can see that I added the ProductNumber and ProductType. for the @UpdatedProducts table Im assuming you dont have the product Number, if you do then you will do the direct merge with no problem, if you dont you would need to find it.

so lets first update ProductNumber in @UpdatedProducts

;with cte as (
    select u.ProductID,u.ProductName,u.ProductType,u.Rate 
    ,coalesce(p.ProductNumber,row_number() over (partition by u.ProductType order by u.ProductID)
    +(select max(pp.ProductNumber) from @Products pp where pp.ProductType=u.ProductType)
    -(select Count(*) from @UpdatedProducts uu 
        inner join @Products ppp on ppp.ProductID=uu.ProductID
        where uu.ProductType=u.ProductType)) [ProductNumber]
    from @UpdatedProducts u
    left outer join @Products p on p.ProductID=u.ProductID
)
update a
    set a.[ProductNumber]=cte.[ProductNumber]
    From @UpdatedProducts a
        inner join cte on cte.ProductID=a.ProductID

I did not find a way to put this in the merge directly.

The result of the @UpdatedProducts after the update will be as below:-

ProductID   ProductName ProductNumber   ProductType Rate
=========   =========== =============   =========== ====
1           Tea         1               Drink       10.00
2           Coffee      2               Drink       25.00
4           Muffin      2               Food        35.00
7           Donut       5               Food        30.00
10          Pizza       8               Food        60.00
11          PizzaLarge  9               Food        80.00

So now we can do a direct merge, as below:-

--Synchronize the target table with refreshed data from source table
MERGE @Products AS TARGET
USING @UpdatedProducts AS SOURCE 
ON (TARGET.ProductID = SOURCE.ProductID) 
--When records are matched, update the records if there is any change
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName OR TARGET.Rate <> SOURCE.Rate 
    THEN UPDATE SET TARGET.ProductName = SOURCE.ProductName, TARGET.Rate = SOURCE.Rate ,TARGET.ProductNumber= TARGET.ProductNumber --left alone on edit
--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET 
THEN INSERT (ProductID, ProductName, Rate,ProductNumber,ProductType) 
    VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate,SOURCE.ProductNumber,SOURCE.ProductType)-- increments every time a record is added 
--When there is a row that exists in target and same record does not exist in source then delete this record target
WHEN NOT MATCHED BY SOURCE 
THEN DELETE 
--$action specifies a column of type nvarchar(10) in the OUTPUT clause that returns 
--one of three values for each row: 'INSERT', 'UPDATE', or 'DELETE' according to the action that was performed on that row
OUTPUT $action, 
DELETED.ProductID AS TargetProductID, 
DELETED.ProductName AS TargetProductName, 
DELETED.Rate AS TargetRate, 
INSERTED.ProductID AS SourceProductID, 
INSERTED.ProductName AS SourceProductName, 
INSERTED.Rate AS SourceRate; 


SELECT * FROM @Products

The result of @Products would be as below:-

ProductID   ProductName ProductNumber   ProductType Rate
=========   =========== =============   =========== ====
1           Tea         1                Drink       10.00
2           Coffee      2                Drink       25.00
4           Muffin      2                Food        35.00
7           Donut       5                Food        30.00
10          Pizza       8                Food        60.00
11          PizzaLarge  9                Food        80.00

For the product numbers (1,3,4,6,7) were all skipped and the new food product Pizza took Product number 8 and continued to be Product 9 for the PrizzaLarge. hope this helps.