Adventures with OUTPUT, MERGE and INSTEAD OF triggers

This StackOverflow question was really interesting for me.
Not because of the users question itself, I thought it was a terrible premise all around to assume that an index violation would lead to the population of an identity value. No, what I found fascinating was my dive into

• Unique index with IGNORE_DUP_KEY set
• OUTPUT clause
• MERGE statement
• INSTEAD OF triggers

Excluding the ignore duplicate key, I've used all of these to some extent in my professional career, but never tried to use them all at once.

Highlights of my findings

• MERGE statement with OUTPUT clause seemed to offer the best fit for what the questioner wanted
• OUTPUT will not have access to the inserted logical table even though the insert will have consumed an identity value and rolled back
• You can fake it through an INSTEAD OF trigger but it's an ugly hack and you wouldn't able to take advantage of the OUTPUT clause
• The inserted virtual table within an INSTEAD OF trigger has identity values populated with 0
• You cannot update the inserted/deleted logical tables
• The OUTPUT clause on a table with an instead of trigger operates on the virtual tables

-- 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
GO

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

Results

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