Errorhandling in batches

Hi there,

I would like to know, if there is a way to handle errors in a batch without the batch being stopped? Take next example:

Two tabeles, SOURCE and DEST, both with columns ID as INT(primary key) and NAME as varchar(20).

When I do this: this statement will break with a duplicate key violation error when an existing

insert into DEST

select ID, Name

from SourceTable

This statement will break with a duplicate key violation error when an existing value is entered. Is there any way I can prevent this from happening? I am aware that with something like SELECT @.error = @.@.error and @.rowcount = @.@.rowcount I can find out what went wrong and locate the offending row. However, sofar I am not able to reset @.@.error to 0 again within the bacth.

Is this at all possible? I can make something like this with a CURSOR, but would like not to use this technique. Basically I want the offending records inserted into antother(error)table to deal with after the batch has completed.

TIA,

FreeHansje

First, select out the 'good' rows, and then the 'bad' rows.

INSERT INTO Dest
SELECT ID, Name

FROM SourceTable s

JOIN Dest d

ON s.ID <> d.ID

INSERT INTO DupIDs

SELECT ID, Name

FROM SourceTable s

JOIN Dest d

ON s.ID = d.ID

|||

Tnx for responding, Arnie.

This sure is a very good solution. This is however just an example, other errors may occur. I'd have to think up all possible errors and check the data for specifically those. I was hoping for a general way of isolating those error-records in the statement itself. But more and more I believe that's not possible.

Tnx again,