Is there a way to insert a record in SQL server if it does not match the latest version of the record based on three of the columns?

Consider the following table named UserAttributes:

+----+--------+----------+-----------+
| Id | UserId | AttrName | AttrValue |
+----+--------+----------+-----------+
|  4 |      1 | FavFood  | Apples    |
|  3 |      2 | FavFood  | Burgers   |
|  2 |      1 | FavShape | Circle    |
|  1 |      1 | FavFood  | Chicken   |
+----+--------+----------+-----------+

I would like to insert a new record in this table if the latest version of a particular attribute for a user has a value that does not match the latest.

What I mean by the latest is, for example, if I was to do: SELECT TOP(1) * FROM [UserAttributes] WHERE [UserId] = 1 AND [AttrName] = 'FavFood' ORDER BY [Id] DESC

I will be able to see that user ID 1’s current favorite food is “Apples”.

Is there a query safe for concurrency that will only insert a new favorite food if it doesn’t match the current favorite food for this user?

I tried using the MERGE query with a HOLDLOCK, but the problem is that WHEN MATCHED/WHEN NOT MATCHED, and that works if I never want to insert a new record after a user has previously set their favorite food (in this example) to the new value. However, it does not consider that a user might switch to a new favorite food, then subsequently change back to their old favorite food. I would like to maintain all the changes as a historical record.

In the data set above, I would like to insert a new record if the user ID 1’s new favorite food is “Burgers”, but I do not want to insert a record if their new favorite food is “Apples” (since that is their current favorite food). I would also like to make this operation safe for concurrency.

Thank you for your help!

EDIT: I should probably also mention that when I split this operation into two queries (ie: first select their current favorite food, then do an insert query only if there is a new food detected) it works under normal conditions. However, we are observing race conditions (and therefore duplicates) since (as you may have guessed) the data set above is simply an example and there are many threads operating on this table at the same time.

Answer

A bit ugly, but to do it in one command, you could insert the user’s (new) favorite food but filter with an EXCEPT of their current values.

e.g., (assuming the user’s new data is in @UserID, @FavFood

; WITH LatestFavFood AS
    (SELECT TOP(1) UserID, AttrName, AttrValue 
      FROM  [UserAttributes] 
      WHERE [UserId] = @UserID AND [AttrName] = 'FavFood' 
      ORDER BY [Id] DESC
     )
INSERT INTO UserAttributes (UserID, AttrName, AttrValue)
    SELECT  @UserID, 'FavFood', @FavFood
  EXCEPT
    SELECT  UserID, AttrName, AttrValue
    FROM    LatestFavFood

Here’s a DB_Fiddle with three runs.

EDIT: I have changed the above to assume varchar types for AttrName rather than nvarchar. The fiddle has a mixture. Would be good to ensure you get them correct (especially food as it may have special characters).