CODEWITHSUNDEEP
sqlsql-serversql-server-2008upsertsql-merge
Catalin Lupuleti
Catalin Lupuleti

Reputation: 37

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

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?

Upvotes: 0

Views: 747

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

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)

Upvotes: 1

Yugandhar
Yugandhar

Reputation: 87

--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
            );

Upvotes: 1

Related Questions