避免插入SQLSERVER上的唯一冲突-AvoiduniqueconflictsoninsertSQLSERVER

1  

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

MERGE语句是处理此类情况的SQL标准方法。

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.

关系数据库管理系统使用SQL MERGE(也称为upsert)语句来INSERT新记录或UPDATE现有记录,具体取决于条件是否匹配。它在SQL:2003标准中正式引入,并在SQL:2008标准中进行了扩展。

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.

它比你的解决方案更冗长,但我觉得它更具可读性。此外,代码的意图非常明确,因为MERGE语句专门处理这个确切的场景。

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.* ;

0  

In this query, distinct will also work in place of top 1

在此查询中,distinct也可用于代替top 1

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