IGNORE Duplicates while inserting NEW Records

SQL Constraint IGNORE_DUP_KEY on Update

The below example gives you a better identification on IGNORE_DUP_KEY.

      Specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE. The default is OFF.

IMPORTANT Note:

         IGNORE_DUP_KEY cannot be set to ON  for indexes created on a view, non-unique indexes, XML indexes, spatial indexes, and filtered indexes.

In backward compatible syntax, WITH IGNORE_DUP_KEY is equivalent to WITH IGNORE_DUP_KEY = ON.

ON
A warning message will occur when duplicate key values are inserted into a unique index. Only the rows violating the uniqueness constraint will fail.

OFF
An error message will occur when duplicate key values are inserted into a unique index. The entire INSERT operation will be rolled back

CREATE TABLE #Big (pk int NOT NULL CONSTRAINT PK_Big PRIMARY KEY)
GO
-- Start with values 1 & 5 in the table

INSERT #Big (pk) VALUES (1), (5);

-- Our batch transaction
BEGIN TRANSACTION;
    -- Insert a batch containing some pre-existing rows
    INSERT #Big (pk) VALUES (1), (2), (3), (4), (5);

    -- Show the contents of the table after the insert statement
    SELECT pk FROM #Big;

    -- Show the transaction count
    SELECT tran_count = @@TRANCOUNT;

 -- Rollback
ROLLBACK TRANSACTION;

-- Final table contents
SELECT pk FROM #Big;
GO
-- Tidy up
DROP TABLE #Big;

Result:

CREATE TABLE #Big (pk int NOT NULL CONSTRAINT PK_Big PRIMARY KEY) WITH (IGNORE_DUP_KEY = ON));
GO
-- Start with values 1 & 5 in the table


INSERT #Big (pk) VALUES (1), (5);

-- Our batch transaction
BEGIN TRANSACTION;
    -- Insert a batch containing some pre-existing rows
    INSERT #Big (pk) VALUES (1), (2), (3), (4), (5);

    -- Show the contents of the table after the insert statement
    SELECT pk FROM #Big;

    -- Show the transaction count
    SELECT tran_count = @@TRANCOUNT;

 -- Rollback
ROLLBACK TRANSACTION;

-- Final table contents
SELECT pk FROM #Big;
GO
-- Tidy up
DROP TABLE #Big;

Result:

If we want to add IGNORE_DUP_KEY on primary key existing, first drop the CONSTRAINT then recreate along with WITH(IGNORE_DUP_KEY=ON)
Drop and alter of a table are the examples shown below

CREATE TABLE #Big (pk int not null CONSTRAINT pk_key primary key WITH(IGNORE_DUP_KEY=ON))
INSERT INTO #Big VALUES(1),(2),(3)
INSERT INTO #Big VALUES(2),(5),(6)
ALTER TABLE #BIG DROP CONSTRAINT PK_KEY
alter table #big add constraint pk_key primary key(PK) WITH(IGNORE_DUP_KEY=ON)