I am trying to write a MS SQL Server Upsert query using MERGE

Question

It works for updating an existing row, but it doesn't insert the row when there's no entry.

This is the CREATE TABLE:

    CREATE TABLE [dbo].[Inventory_Update_Hash_Code] ([Product_Id] [int] NOT
    NULL, [Feed_Id] [int] NOT NULL, [Hash_Code] [int] NOT NULL,
    [Last_Updated] [datetime2](0) NOT NULL
    GO

    ALTER TABLE [dbo].[Inventory_Update_Hash_Code] ADD PRIMARY KEY  
    ([Product_Id], [Feed_Id])
    GO

And this is the query:

    MERGE Product_Update_Hash_Code WITH (HOLDLOCK) AS tar 
    USING (SELECT Feed_Id, Product_Id FROM Product_Update_Hash_Code WHERE
    Feed_Id = 261 AND Product_Id = 300) AS source   
    ON (tar.Feed_Id = source.Feed_Id AND tar.Product_Id = source.Product_Id) 
    WHEN MATCHED THEN 
    UPDATE SET tar.Hash_Code = 55, tar.Last_Updated = SYSUTCDATETIME() 
    WHEN NOT MATCHED
    THEN INSERT (Feed_Id, Product_Id, Last_Updated, Hash_Code) 
    VALUES (261, 300, SYSUTCDATETIME(), 55); 

It looks like the "UNMATCHED" clause doesn't get executed. Did I get this wrong?

Answer 1

You shouldn't be using the table, itself as your source - how can you expect it to find missing rows if you're using rows in that table as your source.

Instead, you should be sourcing your input data independently, either from an actual data table or by using a VALUES clause:

MERGE [Inventory_Update_Hash_Code] WITH (HOLDLOCK) AS tar 
USING (VALUES (261,300,55,SYSUTCDATETIME())) AS
      source (Feed_Id,Product_ID,Hash_Code,Last_Updated)
ON (tar.Feed_Id = source.Feed_Id AND tar.Product_Id = source.Product_Id) 
WHEN MATCHED THEN 
UPDATE SET tar.Hash_Code = source.Hash_Code, tar.Last_Updated = source.Last_Updated
WHEN NOT MATCHED
THEN INSERT (Feed_Id, Product_Id, Hash_Code, Last_Updated) 
VALUES (Feed_Id,Product_ID,Hash_Code,Last_Updated); 

(Note that there was a table name mismatch in your question between the CREATE TABLE and the MERGE. I've switched to the CREATE TABLE name so that I could minimize changes required to run your code)