Orsdxy

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?

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

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        |