Как игнорировать ошибку "Duplicate Key" в T-SQL (SQL Server)

Ответ 1

Хотя я настоятельно рекомендую вам структурировать ваш sql, чтобы не пытаться дублировать вставки (возможно, фрагмент Филиппа Келли, что вам нужно), я хочу упомянуть, что ошибка в инструкции не обязательно вызывает откат.

Если XACT_ABORT не соответствует ON, транзакция не будет автоматически откатываться, если возникнет ошибка, если она не будет достаточно серьезной, чтобы убить соединение. XACT_ABORT по умолчанию OFF.

Например, следующий sql успешно вставляет в таблицу три значения:

create table x ( y int not null primary key )

begin transaction
insert into x(y)
values(1)
insert into x(y)
values(2)
insert into x(y)
values(2)
insert into x(y)
values(3)
commit

Если вы не устанавливаете XACT_ABORT, на клиенте возникает ошибка, вызывающая откат. Если по какой-то ужасной причине вы не можете избежать вставки дубликатов, вы должны уловить ошибку на клиенте и проигнорировать ее.

Ответ 2

Я думаю, что вы ищете опцию IGNORE_DUP_KEY в своем индексе. Посмотрите опцию IGNORE_DUP_KEY ON, зарегистрированную на http://msdn.microsoft.com/en-us/library/ms186869.aspx, которая вызывает попытки повторной вставки для создания предупреждения вместо ошибки.

Ответ 3

Развернув комментарий к ответу SquareCog, вы можете сделать:

INSERT INTO X VALUES(Y,Z)    WHERE Y  NOT IN (SELECT Y FROM X)
INSERT INTO X2 VALUES(Y2,Z2) WHERE Y2 NOT IN (SELECT Y FROM X2)
INSERT INTO X3 VALUES(Y3,Z3) WHERE Y3 NOT IN (SELECT Y FROM X3)

Здесь я предполагаю, что столбец Y присутствует во всех трех таблицах. Обратите внимание, что производительность будет неудовлетворительной, если таблицы не индексируются на Y.

О да, у Y есть уникальное ограничение на него - поэтому они индексируются, и это должно выполняться оптимально.

Ответ 4

Если в разделе "Игнорировать повторяющиеся записи ошибок", чтобы прервать текущую инструкцию и перейти к следующему утверждению без прерывания trnsaction, просто установите BEGIN TRY.. END TRY вокруг каждого утверждения:

BEGIN TRY
    INSERT ...
END TRY
BEGIN CATCH /*required, but you dont have to do anything */ END CATCH
...

Ответ 5

Я хотел бы услышать следующее: Если 99% ваших данных будет вставляться без ошибок, сделав выбор заранее, это приведет к огромному снижению производительности (например, в моем случае от 200 строк/сек до 20 строк в секунду) по сравнению с "немыми" вставками и улавливая случайную ошибку.

После игнорирования ошибок "Нарушение PRIMARY KEY constraint" все вернулось к тому, чтобы быть узким в других ресурсах (уровень запаса определяется как "то, что не хватает для ресурсов узких мест" ).

Вот почему я впервые приземлился на эту дискуссию.

Ответ 6

OK. После проверки некоторых ошибок обработки, я понял, как решить проблему, которую я имел.

Вот пример того, как сделать эту работу (дайте мне знать, если что-то мне не хватает):

SET XACT_ABORT OFF ; -- > really important to set that to OFF
BEGIN
DECLARE @Any_error int
DECLARE @SSQL varchar(4000)
BEGIN TRANSACTION
    INSERT INTO Table1(Value1) VALUES('Value1')
    SELECT @Any_error = @@ERROR
    IF @Any_error<> 0 AND @Any_error<>2627 GOTO ErrorHandler

    INSERT INTO Table1(Value1) VALUES('Value1')
    SELECT @Any_error = @@ERROR
    IF @Any_error<> 0 AND @Any_error<>2627 GOTO ErrorHandler

    INSERT INTO Table1(Value1) VALUES('Value2')
    SELECT @Any_error = @@ERROR
    IF @Any_error<> 0 AND @Any_error<>2627 GOTO ErrorHandler

    ErrorHandler: 
       IF @Any_error = 0 OR @Any_error=2627
       BEGIN 
           PRINT @ssql 
           COMMIT TRAN
       END
       ELSE 
       BEGIN 
           PRINT @ssql 
           ROLLBACK TRAN 
       END
END

В результате вышеуказанной транзакции Table1 будет иметь следующие значения Value1, Value2.

2627 - код ошибки для Duplicate Key.

Спасибо всем за быстрый ответ и полезные предложения.

Ответ 7

INSERT INTO KeyedTable(KeyField, Otherfield)
SELECT n.* FROM 
    (SELECT 'PossibleDupeLiteral' AS KeyField, 'OtherfieldValue' AS Otherfield
     UNION ALL
     SELECT 'PossibleDupeLiteral', 'OtherfieldValue2'
    )
LEFT JOIN KeyedTable k
    ON k.KeyField=n.KeyField
WHERE k.KeyField IS NULL

Это говорит Серверу искать те же данные (надеюсь, тот же самый быстрый способ проверить двойные ключи) и вставить ничего, если он найдет его.

Мне также нравится решение IGNORE_DUP_KEY, но тогда любой, кто полагается на ошибки, чтобы поймать проблемы, будет мистифицирован, когда сервер молча игнорирует их ошибки обмана.

Причина, по которой я выбираю это решение Philip Kelley, заключается в том, что вы можете предоставить несколько рядов данных и получить только те из них, которые отсутствуют:

Ответ 8

Ключи должны быть уникальными. Не делай этого. Редизайн по мере необходимости.

(если вы пытаетесь вставить, а затем удалить, а вставка не удалась... просто выполните удаление сначала. Откат по ошибке в любом из операторов).

Ответ 9

Я пришел сюда, потому что пытался сделать то же самое; Я знал, что у меня были обманы в исходных данных, но я хотел только обновить целевые данные и не добавлять их.

Я думаю, что MERGE отлично работает здесь, потому что вы можете ОБНОВЛЯТЬ или УДАЛИТЬ вещи, которые отличаются друг от друга, и INSERT, которые отсутствуют.

Я закончил это, и он отлично поработал. Я использую SSIS для циклического преобразования файлов Excel и загрузки их в таблицу SQL "RAW" с обманами и всеми. Затем я запускаю MERGE, чтобы объединить "сырую" таблицу с производственной таблицей. Затем я ПРОВЕРЬТЕ "сырую" таблицу и перейдем к следующему файлу Excel.

Ответ 10

Используйте IGNORE_DUP_KEY = OFF во время определения первичного ключа, чтобы игнорировать дубликаты во время вставки. например

create table X( col1.....)

CONSTRAINT [pk_X] PRIMARY KEY CLUSTERED 
(
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY]

Ответ 11

Для SQL Server 2000:

     INSERT INTO t1 (ID, NAME)
      SELECT valueid, valuename
      WHERE  NOT EXISTS
               (SELECT 0
                FROM   t1 as t2
                WHERE  t2.ID = valueid AND t2.name = valuename)

Ответ 12

Ну, вы можете решить это с помощью таблицы temp.

DECLARE @RoleToAdds TABLE
([RoleID] int, [PageID] int)

INSERT INTO @RoleToAdds ([RoleID], [PageID])
    VALUES
    (1, 2),
    (1, 3),
    (1, 4),
    (2, 5)

INSERT INTO [dbo].[RolePages] ([RoleID], [PageID])
    SELECT rta.[RoleID], rta.[PageID] FROM @RoleToAdds rta WHERE NOT EXISTS 
        (SELECT * FROM [RolePages] rp WHERE rp.PageID = rta.PageID AND rp.RoleID = rta.RoleID)

Это может не работать для больших объемов данных, но для нескольких строк он должен работать!