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