如何忽略“重复键"T-SQL (SQL Server) 中的错误 [英] How to Ignore "Duplicate Key" error in T-SQL (SQL Server)

问题描述

我有一个包含多个 SQL 语句(插入、更新和/或删除)的事务.执行时，我想忽略重复错误语句并继续执行下一条语句.这样做的最佳方法是什么?

I have a transaction that contains multiple SQL Statements (INSERT, UPDATE and/or DELETES). When executing, I want to ignore Duplicate Error statements and continue onto the next statement. What's the best way of doing that?

推荐答案

虽然我对您的重点建议是构建您的 sql 以免尝试重复插入(Philip Kelley 的代码段可能是您需要的)，但我想提一下语句中的错误不一定会导致回滚.

Although my emphatic advice to you is to structure your sql so as to not attempt duplicate inserts (Philip Kelley's snippet is probably what you need), I want to mention that an error on a statement doesn't necessarily cause a rollback.

除非XACT_ABORT 为ON，否则在遇到错误时事务不会自动回滚，除非它严重到足以终止连接.XACT_ABORT 默认为 OFF.

Unless XACT_ABORT is ON, a transaction will not automatically rollback if an error is encountered unless it's severe enough to kill the connection. XACT_ABORT defaults to OFF.

例如下面的sql成功插入了三个值到表中:

For example, the following sql successfully inserts three values into the table:

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，否则客户端会出现错误并导致回滚.如果由于某些可怕的原因您无法避免插入重复项，您应该能够在客户端捕获错误并忽略它.

Unless you're setting XACT_ABORT, an error is being raised on the client and causing the rollback. If for some horrible reason you can't avoid inserting duplicates, you ought to be able to trap the error on the client and ignore it.

这篇关于如何忽略“重复键"T-SQL (SQL Server) 中的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！