Orsdxy

I have a 'tmp' table shown below.

In the above table

IGNORE_DUP_KEY is set to ON

And "id" column is the primary key. Table has only one key. After inserting lots of data I will get "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 record which is failed while insertion because of duplicate primary key.

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        |

You could capture all the PK exceptions using a trace or you could capture which sql statements trigger the duplicate key message with an extended event.

In the following examples it shows you how to either capture the 'Exception' with a profiler trace, or capture the sql text executed that triggers the duplicate key message using an extended event.

The difference being that the 'exception' trace gets a row for each violation, which we could log to a file, and then read from that file.

What could you capture?

Before the user error message is returned,a PK violation occurs internally:

Which in turns gives the duplicate key value:

Violation of PRIMARY KEY constraint 'PK__ignore_d__3BD0198E9F9BACEA'. Cannot insert duplicate key in object 'dbo.ignore_dup_key'. The duplicate key value is (1).

While the user does not see this message, we could either capture these with a trace or an extended event.

Profiler trace on the exceptions on the table

Add a filter

Capture the PK violations, even when IGNORE_DUP_KEY = ON

Error message captured

Violation of PRIMARY KEY constraint 'PK__ignore_d__3BD0198E9F9BACEA'. Cannot insert duplicate key in object 'dbo.ignore_dup_key'. The duplicate key value is (1).

The problem here is that it can get messy real fast, as it gives a record per Failed value, so if 1 and 2 already exist

INSERT INTO ignore_dup_key(a) VALUES(1), (2)

It gives two new exceptions in the profiler trace:

1)

Violation of PRIMARY KEY constraint 'PK__ignore_d__3BD0198E9F9BACEA'. Cannot insert duplicate key in object 'dbo.ignore_dup_key'. The duplicate key value is (1).

2)

Violation of PRIMARY KEY constraint 'PK__ignore_d__3BD0198E9F9BACEA'. Cannot insert duplicate key in object 'dbo.ignore_dup_key'. The duplicate key value is (2).

Saving it to a table

Select from the new table

SELECT *

  FROM [my_test].[dbo].[FindViolations];

So, when Inserting 1000 Duplicate ID's, the logging will hold 1000 extra records

  TRUNCATE TABLE [my_test].[dbo].[FindViolations];



  INSERT INTO ignore_dup_key(a) 

  select a from ignore_dup_key; -- 1000 of the same records



  SELECT COUNT(*) from [my_test].[dbo].[FindViolations];

Result

(No column name)

1000

ETC.

Create the extended event

Do not choose a template

Select the error_reported event

Select the SQL_TEXT and username, and any additional things you would want to capture

Result

You could also add a filter, as to filter out non-duplicate key errors