Hi!

> Can someone clarify the following please:
>
> If you are about to post a batch of 1000+ inserts and you are pretty sure that 1 or more may fail what is the most efficient way of doing this? i.e. I'm interested in the case where you don't care whether all the inserts succeeds *as long as the ones that can do*.
>
> Normally the entire batch must succeed or else the transaction has to be rolled back. But is it true that if the inserts are executed within a stored procedure and the sp catches (and ignores the error) then you will get very fast inserts via one transaction rather than the much slower method of wrapping each insert in its own transaction?
>
> If not, is there another way to do this in one transaction?

If you handle the exception inside your PSQL code (even if you do
nothing in the WHEN block, then the transaction won't be rolled back. An
example:

SET TERM ^^ ;
CREATE PROCEDURE P_T1_EXCEPTION_HANDLING
AS
declare i Integer;
begin
delete from t1;

i = 0;
while (i < 10) do
begin
i = i + 1;
insert into t1 (id) values (:i);
if (i = 1) then
begin
insert into t1 (id) values (:i);
end
when any do
begin
end
end
end ^^
SET TERM ; ^^


The stored procedure will insert 10 records, although for i = 1 the
insert into will be executed again, ending in a primary key constraint
violation. Without the WHEN ANY block, the transaction will be rolled
back, thus no records will be inserted at all.

If you are using Firebird 2.5, then you could use the new autonomous
transaction feature in PSQL, which basically allows you to run PSQL code
in its own transaction context, without affecting the client transaction.

If something fails due to primary key / unique constraints violations,
for example when inserting duplicate primary key values, you could also
check out the UPDATE OR INSERT INTO statement or the MERGE statement.
Available since, AFAIR, Firebird 2.1.


--
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions
http://www.upscene.com
My blog:
http://blog.upscene.com/thomas/