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?

Are you using MySQL, MS SQL Server? Or MSQL...? Don't tag products not involved. — jarlh, Mar 16 '17 at 09:14
I am sorry. I've added MySQL by mistake. It's Microsoft SQL 2008. — Catalin Lupuleti, Mar 16 '17 at 09:17
It's rarely correct for the target table to also be the source - how do you expect to get an unmatched result when you're using the table itself as a source of rows? — Damien_The_Unbeliever, Mar 16 '17 at 11:25

score 1 · Answer 1 · answered Mar 16 '17 at 09:14

--MayBe Helps this

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 BY TARGET THEN
            INSERT (
            Feed_Id
            ,Product_Id
            ,Last_Updated
            ,Hash_Code
            )
        VALUES (
            261
            ,300
            ,SYSUTCDATETIME()
            ,55
            );

Thanks for your reply ! Unfortunately that didn't work either. — Catalin Lupuleti, Mar 16 '17 at 09:18

score 1 · Accepted Answer · answered Mar 16 '17 at 11:30

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)

thanks ! This seems to be right way of doing it. Thanks for explaining to me what I was doing wrong. — Catalin Lupuleti, Mar 16 '17 at 11:54

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

2 Answers2