How to get the ignored row while IGNORE_DUP_KEY is ON on SQL Server?

I have a tmp table shown below.

In the above table

• IGNORE_DUP_KEY is set to ON

and

• the id column is the primary key.

Said table has only one key. After inserting lots of data I will get the


Duplicate key was ignored.

message because of some redundant data.

I want to check which redundant row was tried to insert. I checked the origin of the message it was sys.messages.

Now, how to store the row which failed while the insertion attempt took place because of the duplicate primary key value?

There would be additional overhead, but one option might be to create an instead of insert trigger which would check for duplicates first and route those to another table.

--demo setup
set nocount on
DROP TABLE IF EXISTS [dbo].[TestTable]
CREATE TABLE [dbo].[TestTable](
    [ID] [int] NOT NULL,
    [ExtraInformation] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Employee_ID] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
) with (IGNORE_DUP_KEY = ON)
) ON [PRIMARY] 
GO

DROP TABLE IF EXISTS [dbo].[TestTableIgnoredDups]
CREATE TABLE [dbo].[TestTableIgnoredDups](
    [ID] [int] NOT NULL,
    [ExtraInformation] [varchar](50) NOT NULL
)
 ON [PRIMARY]
GO

--create INSTEAD OF trigger
CREATE TRIGGER TestTable_InsteadOfInsert ON dbo.TestTable
INSTEAD OF INSERT
AS
BEGIN
    --select rows to be inserted into #temp
    SELECT *
    INTO #temp
    FROM inserted

    --insert rows to TestTableIgnoredDups where primary key already exists
    INSERT INTO TestTableIgnoredDups
    SELECT t.*
    FROM #temp t
    JOIN TestTable tt
        ON tt.id = t.id

    --delete the duplicate rows from #temp
    DELETE t
    FROM #temp t
    JOIN TestTable tt
        ON tt.id = t.id

    --insert rows to TestTableIgnoredDups where duplicates
    --exist on the inserted virtual table, but not necessarily on TestTable
    ;WITH DupsOnInserted
    AS (
        SELECT id
            ,count(*) AS cnt
        FROM #temp
        GROUP BY id
        HAVING count(*) > 1
        )
    INSERT INTO TestTableIgnoredDups
    SELECT t.*
    FROM #temp t
    JOIN DupsOnInserted doi
        ON doi.id = t.id;

    ;WITH DupsOnInserted
    AS (
        SELECT id
            ,count(*) AS cnt
        FROM #temp
        GROUP BY id
        HAVING count(*) > 1
        )
    DELETE t
    FROM #temp t
    JOIN DupsOnInserted doi
        ON doi.id = t.ID

    --insert the remaining rows to TestTable
    INSERT INTO TestTable
    SELECT *
    FROM #temp
END
GO

--verify by trying to insert a duplicate row 
insert into testtable(id,ExtraInformation) values(1,'RowOne')
insert into testtable(id,ExtraInformation) values(1,'RowOneDup')

select * from TestTable
select * from TestTableIgnoredDups

Data from TestTable

| ID | ExtraInformation |
|----|------------------|
| 1  | RowOne           |

Data from TestTableIgnoreDups

| ID | ExtraInformation |
|----|------------------|
| 1  | RowOneDup        |

StackExchange Database Administrators Q#227267, answer score: 6