This sounded like an interesting slog and I'll have this in blog form on Monday but having never touched the ignore_dup_key option, I wanted to see if there was a way to do as the OP asked, if an insert softly fails based on setting ignore_dup_key on a unique index, is there a simple way to "catch" the id.
To summarize the following wall of code
- @TheCodeKing had the best solution in my mind, use a MERGE statement with a basic no-op function for the update
- 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
Code here
-- 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
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
