How to get the ignored row while IGNORE_DUP_KEY is ON on SQL Server?
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 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