Fe5ZQtzE

1--demo setup
2set nocount on
3DROP TABLE IF EXISTS [dbo].[TestTable]
4CREATE TABLE [dbo].[TestTable](
5    [ID] [int] NOT NULL,
6    [ExtraInformation] [varchar](50) NOT NULL,
7 CONSTRAINT [PK_Employee_ID] PRIMARY KEY CLUSTERED 
8(
9    [ID] ASC
10) with (IGNORE_DUP_KEY = ON)
11) ON [PRIMARY] 
12GO
13
14DROP TABLE IF EXISTS [dbo].[TestTableIgnoredDups]
15CREATE TABLE [dbo].[TestTableIgnoredDups](
16    [ID] [int] NOT NULL,
17    [ExtraInformation] [varchar](50) NOT NULL
18)
19 ON [PRIMARY]
20GO
21
22--create INSTEAD OF trigger
23CREATE TRIGGER TestTable_InsteadOfInsert ON dbo.TestTable
24INSTEAD OF INSERT
25AS
26BEGIN
27
28    --select rows to be inserted into #temp
29    SELECT *
30    INTO #temp
31    FROM inserted
32
33    --insert rows to TestTableIgnoredDups where primary key already exists
34    INSERT INTO TestTableIgnoredDups
35    SELECT t.*
36    FROM #temp t
37    JOIN TestTable tt
38        ON tt.id = t.id
39
40    --delete the duplicate rows from #temp
41    DELETE t
42    FROM #temp t
43    JOIN TestTable tt
44        ON tt.id = t.id
45
46    --insert the remaining rows to TestTable
47    INSERT INTO TestTable
48    SELECT *
49    FROM #temp
50END
51GO
52
53--verify by trying to insert a duplicate row 
54insert into testtable(id,ExtraInformation) values(1,'RowOne')
55insert into testtable(id,ExtraInformation) values(1,'RowOneDup')
56
57select * from TestTable
58select * from TestTableIgnoredDups
59	
60| ID | ExtraInformation |
61|----|------------------|
62| 1  | RowOne           |
63	
64| ID | ExtraInformation |
65|----|------------------|
66| 1  | RowOneDup        |