Avoid unique conflicts on insert SQL SERVER

Question

I'm trying to insert into a table that has unique constraint on (ProductType, ProductOwnerid) in a way that when that key already exists that the contraint violation would not be triggered. So I have this SQL that works as intended:

INSERT INTO dbo.Products (ProductType, ProductOwnerId)
SELECT TOP 1 22, 44  FROM dbo.products prods
WHERE NOT EXISTS (
    SELECT prods2.ProductType FROM dbo.products prods2 
    WHERE prods2.ProductType = 22 AND prods2.ProductOwnerId = 44)

Is this decent SQL or how could I improve this? I'm not a big fan of the TOP 1, how can I make this more readable/better performing?

Answer 1

The MERGE statement is the SQL standard way to handle such cases.

A relational database management system uses SQL MERGE (also called upsert) statements to INSERT new records or UPDATE existing records depending on whether or not a condition matches. It was officially introduced in the SQL:2003 standard, and expanded in the SQL:2008 standard.

It is a little bit more verbose than your solution but I find it more readable. Also, the intention of the code is very clear since the MERGE statement is specialized in dealing with this exact scenario.

CREATE TABLE products (
    ProductId INT IDENTITY(1, 1), 
    ProductType int, ProductOwnerId INT, 

    CONSTRAINT [unq_type_owner] UNIQUE (ProductType, ProductOwnerId)
);

MERGE INTO dbo.products p
USING (VALUES (22, 44))  AS source(ProductType, ProductOwnerId)
ON p.ProductType = source.ProductType AND p.ProductOwnerId = source.ProductOwnerId

WHEN NOT MATCHED THEN
    INSERT (ProductType, ProductOwnerId)
    VALUES (source.ProductType, source.ProductOwnerId)

-- OPTIONAL OUTPUT
OUTPUT $action, Inserted.* ;