How to Ignore "Duplicate Key" error in T-SQL (SQL Server)
sql, sql-server
Solution
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.
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`.
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
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.
Problem
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?