How to avoid inserting duplicate values ​​into a composite Primary Key in an INSERT statement in MS SQL?

Question

Transact SQL

Good time of the day!
More detailed question can be described as follows:
Let's say I have a table TestTable, with Id int, PDate datetime, SomeAnotherFields.
The Primary Key in this table is Id + PDate.
So, let's say I need to insert a lot of values ​​into this table and there is a possibility that some of the values ​​to insert will result in an error due to inserting a duplicate PK.
How can you catch this problem in Insert and ignore it, for example. I read about INSERT IGNORE and INSERT ...ON DUPLICATE KEY UPDATE, but this is for MySQL, but I need it for MS SQL.
I thought I could write CHECK NOT EXISTS(Select 1 from TestTable Where Id = 'here I don't know how to pass the currently inserted value' and PDate = 'the same garbage, only for PDate')
Although even if you write such a CHECK, it will stall the entire insertion procedure, and I need the insertion to continue, just so that the "bad" line is not inserted.

Answer 1

Hello.
I came up with three ways:
1) Try setting the IGNORE_DUP_KEY parameter
2) MSSQL has the MERGE statement
Here is an example of usage:

merge into [dbo].[table]
using [dbo].[Stage_Table] on Stage_Table.pk = table.pk
when not matched then insert (val1) values (1234);

3) Use NOT EXISTS
Here is an example of usage:

INSERT INTO my_table
SELECT 1, 'foo', 3
WHERE NOT EXISTS (
  SELECT 1 from my_table WHERE foo_col = 'foo'
);

How do you insert data into a table? Do you have another table? Or some file that you loop through and then insert records into the table?

Answer 2

By the way, it is possible that the original inserted data also contains duplicates, so in addition to the checks proposed by heartdevil, it is worth checking for duplicates what is being inserted:

-- DROP TABLE TestInsert
CREATE TABLE TestInsert (ID INT NOT NULL, DATA NVARCHAR(3) NOT NULL, CONSTRAINT [PK_TestInsert__ID_DATA] PRIMARY KEY CLUSTERED ([ID],[DATA])  );

INSERT INTO TestInsert 
VALUES  (1, '123')
       ,(2, '222');

-- DROP TABLE #InsertSource
CREATE TABLE #InsertSource (ID INT NOT NULL, DATA NVARCHAR(3) NOT NULL);

INSERT INTO #InsertSource 
VALUES (1, '123')  -- имеются в базе
        ,(2,'222')  -- имеются в базе
        ,(3,'555')
        ,(3,'555')


-- 1) WHERE NOT EXISTS
INSERT INTO TestInsert  (ID, DATA)
SELECT ID , DATA FROM #InsertSource tis WHERE NOT EXISTS(SELECT TOP 1 1 FROM TestInsert ti WHERE ti.ID = tis.ID AND ti.DATA = tis.DATA)
-- тут будет ошибка Cannot insert duplicate key на (3,'555')

-- 2) MERGE
MERGE INTO TestInsert ti
USING #InsertSource tis ON (ti.ID = tis.ID AND ti.DATA = tis.DATA) 
WHEN not matched THEN 
  INSERT (ID, DATA) VALUES (tis.ID, tis.DATA);
-- тут будет ошибка Cannot insert duplicate key на (3,'555')


-- как пример избавление от дубликатов ключей:
;WITH NumValues AS (
  SELECT tis.ID, tis.DATA, RN = ROW_NUMBER() OVER ( PARTITION BY tis.ID, tis.DATA ORDER BY NEWID() )
  FROM #InsertSource AS tis
  )
INSERT INTO TestInsert (ID, DATA)
SELECT ID , DATA FROM NumValues nv 
WHERE NOT EXISTS(SELECT TOP 1 1 FROM TestInsert ti WHERE ti.ID = nv.ID AND ti.DATA = nv.DATA)
AND nv.RN = 1  -- только 1-е строки среди совпавших tis.ID, tis.DATA