由于重复而插入失败后选择身份

Question

假设我有一个 MS SQL Server 表，其中包含主键 ID 和唯一键名称。另外，名称设置为 IGNORE_DUP_KEY。我想要做的是插入一个名称，然后获取它的ID，而不管操作执行是否成功。即，如果名称不重复，则采用新的 ID，如果重复，则采用已存在的 ID。

显然，如果我执行 INSERT Name 然后选择 IDENTITY，那么在 Name 重复的情况下将无法正常工作（像 SCOPE_IDENTITY() 这样的函数在这种情况下将返回 NULL）。不过，我相信应该有一种方法可以轻松实现这一点，因为 INSERT 应该已经找到了未成功插入的 Name 的 ID。因此，该 ID 应在 O(1) 时间内可用。

另外，请注意，由于性能问题，向服务器发出两个单独的请求（插入和选择）对我来说不是一个选择。

Answer 1

IGNORE_DUP_KEY 表示如果在插入行时发现重复键，则插入行将被丢弃，并显示警告而不是错误，而其余行将继续插入。

结果是重复行不受插入语句的影响，因此没有可读取的标识。不确定你的意图到底是什么，但我认为你不能用一个声明来做到这一点。

也许您需要使用 Merge 语句？

Answer 2

这听起来像是一个有趣的slog，我将在周一以博客的形式发布它，但从未接触过ignore_dup_key选项，我想看看是否有办法按照OP的要求去做，如果基于设置ignore_dup_key插入软失败在唯一索引上，是否有一种简单的方法来“捕获”id。

总结一下下面的代码墙

• @TheCodeKing 在我看来是最好的解决方案，请使用 具有用于更新的基本无操作功能的 MERGE 语句
• OUTPUT 将无权访问插入的逻辑表，即使插入将消耗一个标识值并回滚
• 您可以通过 INSTEAD OF trigger 但这是一个丑陋的黑客，你将无法利用 OUTPUT 子句
• 中插入的虚拟表INSTEAD OF 触发器的标识值填充有 0

此处代码

-- This script demonstrates the usage of 
-- IGNORE_DUP_KEY option for an index
-- http://msdn.microsoft.com/en-us/library/ms186869.aspx
-- Why you'd want this behaviour is left as 
-- an excercise to the reader
--

SET NOCOUNT ON

IF EXISTS
(
    SELECT 1 
    FROM sys.tables T 
    WHERE T.name = 'DupesOk' 
        AND T.schema_id = schema_id('dbo')
)
BEGIN
    DROP TABLE dbo.DupesOk
END

CREATE TABLE 
    dbo.DupesOk
(
    dupe_id int identity(1,1) NOT NULL PRIMARY KEY
,   name varchar(50) NOT NULL
)

-- Create an index that is unique but
-- violation of the unique constraint is
-- merely discarded with warning instead of
-- blowing up
CREATE UNIQUE INDEX 
    uq_dupes_name
ON dbo.DupesOk
(
    name
)
WITH IGNORE_DUP_KEY 

-- Add a name and emit the identity value
-- from the inserted virtual table
INSERT INTO
    dbo.DupesOk
OUTPUT
    inserted.dupe_id
,   inserted.name
SELECT
    'Peter Parker'

-- Old-school means of showing the identity
-- values, 1's across the board
-- See earlier posting
-- http://billfellows.blogspot.com/2009/10/scope-and-identity.html
SELECT 
    @@IDENTITY AS six_of_one
,   SCOPE_IDENTITY() half_dozen_of_other
,   IDENT_CURRENT('dbo.DupesOk') AS current_identity_value


-- Add a new name and emit the identity value
-- from the inserted virtual table
INSERT INTO
    dbo.DupesOk
OUTPUT
    inserted.dupe_id
,   inserted.name
SELECT
    'Spider man'

-- Same as above, 2s across the board
SELECT 
    @@IDENTITY AS six_of_one
,   SCOPE_IDENTITY() half_dozen_of_other
,   IDENT_CURRENT('dbo.DupesOk') AS current_identity_value

-- Insert a duplicate value for the unique index
-- watch it not explode with a output message of 
-- 'Duplicate key was ignored.'
INSERT INTO
    dbo.DupesOk
OUTPUT
    -- This won't show anything as there is nothing to show
    inserted.dupe_id
,   inserted.name
SELECT
    'Peter Parker'

-- The first two remain 2's as they belong to the successful
-- insert of Spider man. ident_current shows that the value was
-- incremented. The calling code did not do a lookup to  
SELECT 
    @@IDENTITY AS this_identity_belongs_to_spider_man
,   SCOPE_IDENTITY() this_identity_also_belongs_to_spider_man
    -- As expected, the value is now 3, it got incremented 
,   IDENT_CURRENT('dbo.DupesOk') AS current_identity_value

;
MERGE
    -- target table
    dbo.DupesOk AS T
USING
(
    -- source system
    SELECT 'Hal Jordan' AS name
) AS S
ON S.name = T.name
WHEN
    MATCHED THEN
    UPDATE
    SET 
        T.name = S.name
WHEN
    NOT MATCHED THEN
    INSERT
    (
        [name]
    )
    VALUES
    (
        [name]
    )
-- 4 | Hal Jordan | INSERT
OUTPUT
    inserted.dupe_id
,   inserted.name
,   $action
;


-- 4's as expected
SELECT 
    @@IDENTITY AS hal_jordan
,   SCOPE_IDENTITY() still_hal_jordan
,   IDENT_CURRENT('dbo.DupesOk') AS current_identity_value


-- Add someone else just to get the ids to flip
INSERT INTO
    dbo.DupesOk
OUTPUT
    inserted.dupe_id
,   inserted.name
SELECT
    'Tony Stark'

-- 5's
SELECT 
    @@IDENTITY AS tony_stark
,   SCOPE_IDENTITY() still_tony_stark
,   IDENT_CURRENT('dbo.DupesOk') AS current_identity_value

;
-- Try inserting an existing id 
MERGE
    -- target table
    dbo.DupesOk AS T
USING
(
    -- source system
    SELECT 'Hal Jordan' AS name
) AS S
ON S.name = T.name
WHEN
    MATCHED THEN
    UPDATE
    SET 
        T.name = S.name
WHEN
    NOT MATCHED THEN
    INSERT
    (
        [name]
    )
    VALUES
    (
        [name]
    )
-- 4 | Hal Jordan | UPDATE
OUTPUT
    inserted.dupe_id
,   inserted.name
,   $action
;

-- Still 5's
SELECT 
    @@IDENTITY AS tony_stark
,   SCOPE_IDENTITY() still_tony_stark
,   IDENT_CURRENT('dbo.DupesOk') AS current_identity_value


GO
-- What if we try a trigger?
-- It would need to be an instead of trigger
-- as the value will have already been 
-- http://msdn.microsoft.com/en-us/library/ms189799.aspx
-- http://msdn.microsoft.com/en-us/library/ms175089.aspx
CREATE TRIGGER tr_dupes_insert
ON dbo.DupesOk
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON
    -- variety of different approaches here but
    -- I'll attempt the insert and if no rows
    -- are affected, then we know it's an existing
    -- row and lookup the identity
    DECLARE
        @ident TABLE
    (
        dupe_id int NOT NULL
    ,   name varchar(50) NOT NULL
    )

    -- Only n00bs code triggers for single rows
    INSERT INTO
        dbo.DupesOk
    (
        name
    )
    -- output clause
    -- http://msdn.microsoft.com/en-us/library/ms177564.aspx
    OUTPUT
        -- the output's virtual table
        -- recursion is deep, yo
        inserted.dupe_id
    ,   inserted.name
    INTO
        @ident
    SELECT
        I.name
    FROM
        -- the trigger's virtual table
        -- fascinatingly enough, the value for
        -- an identity field pre-insert on an
        -- instead of trigger is 0 and not NULL
        -- as one would assume
        inserted I

    -- Now we need to add anyone into the 
    -- table variable that didn't get inserted
    -- into @ident in the previous statement
    INSERT INTO
        @ident
    SELECT
        D.dupe_id
    ,   D.name
    FROM
        inserted I
        INNER JOIN
            dbo.DupesOk D
            ON D.name = I.name
        LEFT OUTER JOIN
            @ident tv
            -- can't match on ids here
            -- as they all come in as zero
            ON tv.name = I.name
    WHERE
        tv.dupe_id IS NULL

    SELECT
        I.dupe_id
    ,   I.name
    FROM
        @ident I

    -- To make OUTPUT work correctly, we'd need to
    -- "fix" the values in the inserted virtual tables
    -- but uncommenting this will result in a 
    -- trigger creation error of
    -- "The logical tables INSERTED and DELETED cannot be updated"
    --UPDATE 
    --    I
    --SET
    --    dupe_id = -1
    --FROM
    --    inserted i
    --    INNER JOIN
    --        @ident TV
    --        ON TV.name = i.name
END
GO

DECLARE
    @idents TABLE
(
    dupe_id int 
,   name varchar(50)
)


-- We should see
-- 1 | Peter Parker
-- 7 | Barry Allen
--
-- 6 was consumed by the double pump of Hal Jordan

-- results were surprising, to me at least


INSERT INTO
    dbo.DupesOk
-- this will generate an error
-- The target table 'dbo.DupesOk' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.
-- unless we dump output results into a table
OUTPUT
    inserted.dupe_id
,   inserted.name
INTO
    @idents
SELECT
    'Peter Parker'
UNION ALL
SELECT
    'Barry Allen'

-- The above statement's trigger correctly spits out the rows we emit in the final
-- step of the trigger
-- dupe_id  name
-- 7        Barry Allen
-- 1        Peter Parker

-- Look at this, it's the inserted virtual table
-- from the trigger in pristine condition
-- and there's no way to unbugger it
SELECT * FROM @idents I

结果

dupe_id     name
----------- --------------------------------------------------
1           Peter Parker

six_of_one                              half_dozen_of_other                     current_identity_value
--------------------------------------- --------------------------------------- ---------------------------------------
1                                       1                                       1

dupe_id     name
----------- --------------------------------------------------
2           Spider man

six_of_one                              half_dozen_of_other                     current_identity_value
--------------------------------------- --------------------------------------- ---------------------------------------
2                                       2                                       2

dupe_id     name
----------- --------------------------------------------------
Duplicate key was ignored.

this_identity_belongs_to_spider_man     this_identity_also_belongs_to_spider_man current_identity_value
--------------------------------------- ---------------------------------------- ---------------------------------------
2                                       2                                        3

dupe_id     name                                               $action
----------- -------------------------------------------------- ----------
4           Hal Jordan                                         INSERT

hal_jordan                              still_hal_jordan                        current_identity_value
--------------------------------------- --------------------------------------- ---------------------------------------
4                                       4                                       4

dupe_id     name
----------- --------------------------------------------------
5           Tony Stark

tony_stark                              still_tony_stark                        current_identity_value
--------------------------------------- --------------------------------------- ---------------------------------------
5                                       5                                       5

dupe_id     name                                               $action
----------- -------------------------------------------------- ----------
4           Hal Jordan                                         UPDATE

tony_stark                              still_tony_stark                        current_identity_value
--------------------------------------- --------------------------------------- ---------------------------------------
5                                       5                                       5

Duplicate key was ignored.
dupe_id     name
----------- --------------------------------------------------
7           Barry Allen
1           Peter Parker

dupe_id     name
----------- --------------------------------------------------
0           Peter Parker
0           Barry Allen

Answer 3

如果我正确理解你的问题，你应该考虑创建一个 DML 触发器来处理这个逻辑。

如果我误解了你的问题，请告诉我。

Answer 4

您需要获取 OUTPUT 子句中插入的值。这样做是这样的：

INSERT foo (a, b)
输出插入.a，插入.b