BfvZ0RBR

1ID , Name 
2----  ----
3 1     A
4 2     B
5 3     C
6 1     D
7	
8--demo setup
9set nocount on
10DROP TABLE IF EXISTS [dbo].[TestTable]
11CREATE TABLE [dbo].[TestTable](
12    [ID] [int] NOT NULL,
13    [ExtraInformation] [varchar](50) NOT NULL,
14 CONSTRAINT [PK_Employee_ID] PRIMARY KEY CLUSTERED 
15(
16    [ID] ASC
17) with (IGNORE_DUP_KEY = ON)
18) ON [PRIMARY] 
19GO
20
21DROP TABLE IF EXISTS [dbo].[TestTableIgnoredDups]
22CREATE TABLE [dbo].[TestTableIgnoredDups](
23    [ID] [int] NOT NULL,
24    [ExtraInformation] [varchar](50) NOT NULL
25)
26 ON [PRIMARY]
27GO
28
29--create INSTEAD OF trigger
30CREATE TRIGGER TestTable_InsteadOfInsert ON dbo.TestTable
31INSTEAD OF INSERT
32AS
33BEGIN
34    --select rows to be inserted into #temp
35    SELECT *
36    INTO #temp
37    FROM inserted
38
39    --insert rows to TestTableIgnoredDups where primary key already exists
40    INSERT INTO TestTableIgnoredDups
41    SELECT t.*
42    FROM #temp t
43    JOIN TestTable tt
44        ON tt.id = t.id
45
46    --delete the duplicate rows from #temp
47    DELETE t
48    FROM #temp t
49    JOIN TestTable tt
50        ON tt.id = t.id
51
52    --insert rows to TestTableIgnoredDups where duplicates
53    --exist on the inserted virtual table, but not necessarily on TestTable
54    ;WITH DupsOnInserted
55    AS (
56        SELECT id
57            ,count(*) AS cnt
58        FROM #temp
59        GROUP BY id
60        HAVING count(*) > 1
61        )
62    INSERT INTO TestTableIgnoredDups
63    SELECT t.*
64    FROM #temp t
65    JOIN DupsOnInserted doi
66        ON doi.id = t.id;
67
68    ;WITH DupsOnInserted
69    AS (
70        SELECT id
71            ,count(*) AS cnt
72        FROM #temp
73        GROUP BY id
74        HAVING count(*) > 1
75        )
76    DELETE t
77    FROM #temp t
78    JOIN DupsOnInserted doi
79        ON doi.id = t.ID
80
81    --insert the remaining rows to TestTable
82    INSERT INTO TestTable
83    SELECT *
84    FROM #temp
85END
86GO
87	
88--verify by trying to insert a duplicate row 
89insert into testtable(id,ExtraInformation) values(1,'RowOne')
90insert into testtable(id,ExtraInformation) values(1,'RowOneDup')
91
92select * from TestTable
93	
94| ID | ExtraInformation |
95|----|------------------|
96| 1  | RowOne           |
97	
98select * from TestTableIgnoredDups
99
100| ID | ExtraInformation |
101|----|------------------|
102| 1  | RowOneDup        |