Как в INSERT — выражении в MS SQL избежать вставки дублирующихся значений в составной Primary Key?

Question

[Alexey Sh]

Доброе время суток!

Более развернуто вопрос можно описать так:

Допустим, у меня есть таблица TestTable, с Id int, PDate datetime, SomeAnotherFields.
Primary Key в этой таблице - это связка Id + PDate.
Так вот, допустим, мне нужно вставить множество значений в эту таблицу и есть вероятность того, что некоторые значения для вставки приведут к ошибке, из за вставки дублирующего PK.

Как в Insert можно отловить эту проблему и проигнорить например. Прочитал про INSERT IGNORE и INSERT ...ON DUPLICATE KEY UPDATE, но это для MySQL, а мне надо для MS SQL.

Думал может написать CHECK NOT EXISTS(Select 1 from TestTable Where Id = 'вот тут не знаю как передать вставляемое в данный момент значение' and PDate = 'та же фигня, только для PDate')

Хотя если даже и написать такой CHECK, то он застопорит всю процедуру вставки, а мне нужно, чтобы вставка продолжилась, просто чтобы "плохая" строка не вставлялась.

Answer 1

[heartdevil]

Привет.
Надыбал три способа:

1) Попробуйте установить параметр IGNORE_DUP_KEY

2) В MSSQL есть оператор MERGE

Вот пример использования:

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

3) Использовать NOT EXISTS

Вот пример использования:

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

А как вы данные вставляете в таблицу? У вас есть еще одна таблица? Или какой-то файл, который вы в цикле пробегаете и затем вставляете записи в таблицу?

Comments

[Alexey Sh]

Вариант 1 немного не подходит, так как он просто игнорирует такие ошибки, НО вставляет записи, насколько я понял из мануала. Вариант 2 не понял, что такое Stage_Table и что такое table из вашего примера, но вроде как то, что нужно, покопаюсь. Вариант 3 в принципе может подойти. Спасибо.

[heartdevil]

Alexey Sh: А вам какая разница игнорирует он или нет? Вы же это никак не обрабатываете, насколько я понял. Да это же разовая операция импорта у вас, если я верно понял. Storage_table и table просто две таблицы для объединения.

[Alexey Sh]

Ну в перспективе я хотел перезапускать вставку новой сгенерированной строки для тех строк, которые вставились с ошибкой. Также, если я правильно понял, то строки в первом варианте все равно вставятся, даже с дублирующим ключем. Или я неверно скурил мануал?)

[heartdevil]

Alexey Sh: Нет. дублирующиеся ключи будут просто игнорированы. Вы получите просто предупреждение, но не аварийную ошибку. Вообще говоря, зачем вам такой "неуникальный" ключ? Сделайте сквозную генерацию primary key. Меньше проблем будет).

Answer 2

[Артур Полозов]

Кстати возможна ситуация, что в исходных вставляемых данных так же содержатся дубликаты, поэтому помимо проверок предложенных heartdevil стоит проверять на дубликаты то, что вставляется:

-- 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