6

I have a tmp table shown below.

enter image description here

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?

4

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:

enter image description here

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.

enter image description here


Profiler trace on the exceptions on the table

enter image description here

Add a filter

enter image description here

Capture the PK violations, even when IGNORE_DUP_KEY = ON

enter image description here

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

enter image description here

Select from the new table

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

enter image description here

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

enter image description here ETC.


Create the extended event

enter image description here

Do not choose a template

enter image description here

Select the error_reported event

enter image description here

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

enter image description here

Result

enter image description here

You could also add a filter, as to filter out non-duplicate key errors enter image description here

  • Can we capture for a specific database and table? – V T Vishwanath Jan 16 at 12:55
  • In the profiler trace window you can add additional filters, this is located at events selection --> Column Filters... . There you can add a databasename, and the table filter needs to be added to the TextData filter – Randi Vertongen Jan 16 at 12:58
  • In the case of Extended Events. What if we have bulk insertion ? or using BCP? How to get the exact record which failed. I tried with multiple insert statements and it gives all sql text. Can't find the exact insert statement which failed. – V T Vishwanath Jan 16 at 13:50
  • @VTVishwanath For the moment being I have not found the correct event for capturing all the different failed records, only the statement executed. For now, only by using the profiler trace and logging it to a table was I able to get all the records that failed through T-SQL. – Randi Vertongen Jan 16 at 13:54
6

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        |
  • Can we use any system table to get the info? Because trigger will make server slow if we have large number of rows to be inserted. – V T Vishwanath Jan 16 at 12:06
  • @VTVishwanath - the only way I know to satisfy your requirement of (Now how to store the record which is failed while insertion because of duplicate primary key.) is with an instead of trigger. – Scott Hodgin Jan 16 at 13:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.