Как избежать вставки повторяющихся записей при использовании оператора слияния T-SQL

Я пытаюсь вставить много записей с помощью инструкции MERGE из T-SQL, но мой запрос не вставляет INSERT, если в исходной таблице есть дубликаты записей. Неисправность вызвана:

Целевая таблица имеет первичный ключ на основе двух столбцов
Исходная таблица может содержать дубликаты записей, которые нарушают целевую таблицу. Ограничение основного ключа ( "Нарушение ограничения PRIMARY KEY" ).

Я ищу способ изменить свой оператор MERGE, чтобы он либо игнорировал повторяющиеся записи в исходной таблице, и/или пытался/улавливал оператор INSERT для обнаружения исключений, которые могут возникнуть (т.е. будут выполняться все другие инструкции INSERT независимо от нескольких плохих яиц, которые могут произойти) - или, может быть, есть лучший способ решить эту проблему?

Вот пример запроса того, что я пытаюсь объяснить. В приведенном ниже примере добавляется 100 тыс. Записей в временную таблицу, а затем будет пытаться вставить эти записи в целевую таблицу -

ИЗМЕНИТЬ В моем первоначальном сообщении я включил только два поля в таблицы примеров, которые уступили друзьям SO, чтобы дать решение DISTINCT, чтобы избежать дублирования в заявлении MERGE. Я должен был упомянуть, что в моей реальной проблеме таблицы имеют 15 полей и из этих 15, два из полей - КЛАСТЕРНЫЙ ПЕРВЫЙ КЛЮЧ. Поэтому ключевое слово DISTINCT не работает, потому что мне нужно ВЫБРАТЬ все 15 полей и игнорировать дубликаты на основе двух полей.

Я обновил запрос ниже, чтобы включить еще одно поле, col4. Мне нужно включить col4 в MERGE, но мне нужно только убедиться, что ТОЛЬКО col2 и col3 уникальны.

-- Create the source table
CREATE TABLE #tmp (
col2 datetime NOT NULL,
col3 int NOT NULL,
col4 int
)
GO

-- Add a bunch of test data to the source table
-- For testing purposes, allow duplicate records to be added to this table
DECLARE @loopCount int = 100000
DECLARE @loopCounter int = 0
DECLARE @randDateOffset int
DECLARE @col2 datetime
DECLARE @col3 int
DECLARE @col4 int

WHILE (@loopCounter) < @loopCount
BEGIN
    SET @randDateOffset = RAND() * 100000
    SET @col2 = DATEADD(MI,@randDateOffset,GETDATE())
    SET @col3 = RAND() * 1000
    SET @col4 = RAND() * 10
    INSERT INTO #tmp
    (col2,col3,col4)
    VALUES
    (@col2,@col3,@col4);

    SET @loopCounter = @loopCounter + 1
END

-- Insert the source data into the target table
-- How do we make sure we don't attempt to INSERT a duplicate record? Or how can we 
-- catch exceptions? Or?
MERGE INTO dbo.tbl1 AS tbl
    USING (SELECT * FROM #tmp) AS src
    ON (tbl.col2 = src.col2 AND tbl.col3 = src.col3)
    WHEN NOT MATCHED THEN 
        INSERT (col2,col3,col4)
        VALUES (src.col2,src.col3,src.col4);
GO

Ответ 1

Решится к вашей новой спецификации. Только вставка самого высокого значения col4: на этот раз я использовал группу для предотвращения дублирования строк.

MERGE INTO dbo.tbl1 AS tbl 
USING (SELECT col2,col3, max(col4) col4 FROM #tmp group by col2,col3) AS src 
ON (tbl.col2 = src.col2 AND tbl.col3 = src.col3) 
WHEN NOT MATCHED THEN  
    INSERT (col2,col3,col4) 
    VALUES (src.col2,src.col3,src.col4);

Ответ 2

Учитывая, что источник имеет дубликаты, и вы полностью не используете MERGE, я бы использовал INSERT.

 INSERT dbo.tbl1 (col2,col3) 
 SELECT DISTINCT col2,col3
 FROM #tmp src
 WHERE NOT EXISTS (
       SELECT *
       FROM dbo.tbl1 tbl
       WHERE tbl.col2 = src.col2 AND tbl.col3 = src.col3)

Причина ошибки MERGE заключается в том, что она не проверяется по строкам. Все несоответствия найдены, затем он пытается ВСТАВИТЬ все это. Он не проверяет наличие строк в той же самой партии, которая уже соответствует.

Это напоминает мне немного о "проблема Хэллоуина" , где ранние изменения данных атомной операции влияют на последующие изменения данных: это не правильно

Ответ 3

Вместо GROUP BY вы можете использовать аналитическую функцию, позволяющую объединять определенную запись в наборе повторяющихся записей.

MERGE INTO dbo.tbl1 AS tbl
USING (
    SELECT *
    FROM (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY col2, col3 ORDER BY ModifiedDate DESC) AS Rn
        FROM #tmp
    ) t
    WHERE Rn = 1    --choose the most recently modified record
) AS src
ON (tbl.col2 = src.col2 AND tbl.col3 = src.col3)