Return record(s) that produced error

[naxy]

This is going to be far-fetched, I know, but my immediate supervisor has asked if it would be possible to return any record(s) that causes an error. I told him I would investigate, but I didn't think it was possible.

For example, if we have an INSERT statement to move 100,000 records into a table, but one record doesn't meet the Foreign Key, he wants to be able to get back that one record when the Stored Procedure fails. We're already logging the procedure, error message, and relative area of the procedure, but I guess that's not good enough for him. = )

Thanks!

*Note - We do have checks and balances to ensure that we don't try to push a record that won't meet the constraints of a table. That is just an example.

 

[RobertT687]

Obviously you have error handling that lets your procedure continue from single row failures, so...

Create a new table (tblErrors for example) with NEARLY identical structure as your INSERT table but having no constraints. The table can have additional columns to capture when the failure occurred and other relevant debugging info; probably with an IDENTITY column as the Primary Key.

In your error handler INSERT the failing row into the new table along with any additional debug info.

You will need a way to edit or delete the failed row.

Your main procedure would process tblErrors and try to INSERT any rows it finds there, deleting the error row or otherwise marking the row as used IF the INSERT works; leaving in the table any row still in error.

 

[naxy]

That sounds like a good option. However, how would I be able to determine the problematic row(s)? For example, we have a straight INSERT INTO... SELECT FROM statement. It may process up to 50,000 records. If only one is "invalid", how can I know which one it is (without writing a query that checks all of the constraints, etc... which is something we already have in the WHERE clause of the INSERT)? Hypothetically, we will never need this logic, but again, it has been requested by management.

Thanks!

 

[RobertT687]

One way come to mind.
If the only issue at the point of INSERTion is a primary key violation you could try something like: (Untested)

INSERT INTO tblError 
SELECT s.* 
FROM  SourceTable s 
     INNER JOIN TargetTable t
WHERE s.PrimaryKeyField = t.PrimaryKeyField

Then

INSERT INTO TargetTable
SELECT * 
FROM SourceTable s
WHERE s.PrimaryKeyField NOT IN (Select PrimaryKeyField FROM TargetTable)

Likely there is a better way if source and target contain a large number of rows.