SQL 仅在不存在的情况下插入一行

Only inserting a row if it's not already there

提问by Adam

I had always used something similar to the following to achieve it:

我一直使用类似于以下的东西来实现它：

INSERT INTO TheTable
SELECT
    @primaryKey,
    @value1,
    @value2
WHERE
    NOT EXISTS
    (SELECT
        NULL
    FROM
        TheTable
    WHERE
        PrimaryKey = @primaryKey)

...but once under load, a primary key violation occurred. This is the only statement which inserts into this table at all. So does this mean that the above statement is not atomic?

...但是一旦处于负载状态，就会发生主键冲突。这是唯一插入到该表中的语句。那么这是否意味着上面的语句不是原子的呢？

The problem is that this is almost impossible to recreate at will.

问题是这几乎不可能随意重新创建。

Perhaps I could change it to the something like the following:

也许我可以将其更改为以下内容：

INSERT INTO TheTable
WITH
    (HOLDLOCK,
    UPDLOCK,
    ROWLOCK)
SELECT
    @primaryKey,
    @value1,
    @value2
WHERE
    NOT EXISTS
    (SELECT
        NULL
    FROM
        TheTable
    WITH
        (HOLDLOCK,
        UPDLOCK,
        ROWLOCK)
    WHERE
        PrimaryKey = @primaryKey)

Although, maybe I'm using the wrong locks or using too much locking or something.

虽然，也许我使用了错误的锁或使用了太多的锁或其他东西。

I have seen other questions on stackoverflow.com where answers are suggesting a "IF (SELECT COUNT(*) ... INSERT" etc., but I was always under the (perhaps incorrect) assumption that a single SQL statement would be atomic.

我在 stackoverflow.com 上看到了其他问题，其中的答案暗示了“IF (SELECT COUNT(*) ... INSERT”等），但我始终（可能不正确）假设单个 SQL 语句是原子的。

Does anyone have any ideas?

有没有人有任何想法？

采纳答案by gbn

What about the "JFDI"pattern?

怎么样的“JFDI”模式？

BEGIN TRY
   INSERT etc
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() <> 2627
      RAISERROR etc
END CATCH

Seriously, this is quickest and the most concurrent without locks, especially at high volumes. What if the UPDLOCK is escalated and the whole table is locked?

说真的，这是最快和最并发的，没有锁，尤其是在高容量的情况下。如果UPDLOCK被升级并且整个表被锁定怎么办？

Read lesson 4:

阅读第 4 课：

Lesson 4:When developing the upsert proc prior to tuning the indexes, I first trusted that the If Exists(Select…)line would fire for any item and would prohibit duplicates. Nada. In a short time there were thousands of duplicates because the same item would hit the upsert at the same millisecond and both transactions would see a not exists and perform the insert. After much testing the solution was to use the unique index, catch the error, and retry allowing the transaction to see the row and perform an update instead an insert.

第 4 课：在调整索引之前开发 upsert 过程时，我首先相信该If Exists(Select…)行会为任何项目触发并禁止重复。纳达。在短时间内有数千个重复项，因为同一个项目会在同一毫秒内命中 upsert，并且两个事务都会看到不存在并执行插入。经过多次测试，解决方案是使用唯一索引，捕获错误，然后重试允许事务查看行并执行更新而不是插入。

回答by GSerg

I added HOLDLOCK which wasn't present originally. Please disregard the version without this hint.

我添加了最初不存在的 HOLDLOCK。请忽略没有此提示的版本。

As far as I'm concerned, this should be enough:

就我而言，这应该足够了：

INSERT INTO TheTable 
SELECT 
    @primaryKey, 
    @value1, 
    @value2 
WHERE 
    NOT EXISTS 
    (SELECT 0
     FROM TheTable WITH (UPDLOCK, HOLDLOCK)
     WHERE PrimaryKey = @primaryKey) 

Also, if you actually want to update a row if it exists and insert if it doesn't, you might find this questionuseful.

此外，如果您真的想更新一行（如果存在）并插入（如果不存在），您可能会发现这个问题很有用。

回答by Chris Smith

You could use MERGE:

你可以使用合并：

MERGE INTO Target
USING (VALUES (@primaryKey, @value1, @value2)) Source (key, value1, value2)
ON Target.key = Source.key
WHEN MATCHED THEN
    UPDATE SET value1 = Source.value1, value2 = Source.value2
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Name, ReasonType) VALUES (@primaryKey, @value1, @value2)

回答by pimbrouwers

Firstly, huge shout out to our man @gbn for his contributions to the community. Can't even begin to explain how often I find myself following his advice.

首先，对我们的人@gbn 对社区的贡献大喊大叫。甚至无法开始解释我发现自己遵循他的建议的频率。

Anyway, enough fanboy-ing.

无论如何，足够的狂热。

To add slightly to his answer, perhaps "enhance" it. For those, like me, left feeling unsettled with what to do in the <> 2627scenario (and no an empty CATCHis not an option). I found this little nugget from technet.

稍微增加他的答案，也许“增强”它。对于像我这样的人，对在<> 2627场景中做什么感到不安（并且没有空CATCH不是一种选择）。我从technet 上找到了这个小金块。

    BEGIN TRY
       INSERT etc
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() <> 2627
          BEGIN
                DECLARE @ErrorMessage NVARCHAR(4000);
                DECLARE @ErrorSeverity INT;
                DECLARE @ErrorState INT;

                SELECT @ErrorMessage = ERROR_MESSAGE(),
                @ErrorSeverity = ERROR_SEVERITY(),
                @ErrorState = ERROR_STATE();

                    RAISERROR (
                        @ErrorMessage,
                        @ErrorSeverity,
                        @ErrorState
                    );
          END
    END CATCH

回答by Marcelo Cantos

I don't know if this is the "official" way, but you could try the INSERT, and fall back to UPDATEif it fails.

我不知道这是否是“官方”方式，但您可以尝试INSERT，UPDATE如果失败则退回。

回答by Marc

I've done a similar operation in past using a different method. First, I declare a variable to hold the primary key. Then I populate that variable with the output of a select statement which looks for a record with those values. Then I do and IF statement. If primary key is null, then do insert, else, return some error code.

我过去曾使用不同的方法进行过类似的操作。首先，我声明一个变量来保存主键。然后我用 select 语句的输出填充该变量，该语句查找具有这些值的记录。然后我做和IF语句。如果主键为空，则插入，否则，返回一些错误代码。

     DECLARE @existing varchar(10)
    SET @existing = (SELECT primaryKey FROM TABLE WHERE param1field = @param1 AND param2field = @param2)

    IF @existing is not null
    BEGIN
    INSERT INTO Table(param1Field, param2Field) VALUES(param1, param2)
    END
    ELSE
    Return 0
END