We help IT Professionals succeed at work.

Print primary Key of last record inserted if error occurs

I will be running insert queries on several tables, each of which will include the insertion of thousands of records.  For various reasons, the server hiccups now and then and the query fails.  When it does, I need to know the last record successfully inserted.  The query will, generically speaking, look like this:

insert table123
select emp, date, hours from table 456

if the query errors on emp 'JoeBlow', I want the error message to list JoeBlow as the record that generated the error.

Comment
Watch Question

in that case you can use a cursor to insert the records one at a time and then print the variables you store the values into when recording when it errors out.
T HoecherlDeveloper

Author

Commented:
I was hoping not to have to use a cursor.  I already built a cursor and that does, indeed work, but it is very slow.
one way to improve the cursor speed (tried and tested) is to first create a table variable (@Tbl not #tbl) and write a cursor on the table variable after fetching all records into it.. Works much faster than writing the cursor directly on the table.
Top Expert 2012

Commented:
An INSERT statement is an all or nothing event.  There is no concept of "last record successfully inserted", none of the rows will be added.

Commented:
Unfortunately inserting the rows one at a time and catching any errors is your only option.  Since you are inserting thousands of rows at a time, it is treated as 1 transaction to insert all of those rows.  SQL won't be able to tell you which specific row caused the problem.  It will only tell you that the transaction failed as a result of something in the set of data you tried to insert.

If you don't want to insert them 1 at a time for the entire set of data, you could eliminate some of the overhead by attempting the full insert first.  If it succeeds when you insert the thousand rows then move on.  If not, catch the error and have it insert that set of data 1 row at a time and catch any errors that result. I mocked up some pseudo code of what I'm talking about below.

 
--quasi-pseudocode

DECLARE @iMIN   INT
DECLARE @iMAX   INT
DECLARE @i      INT

SET @iMIN = 1000
SET @iMAX = 2000

SET @i = @iMIN

BEGIN TRY
    --Try the whole insert first.
    INSERT INTO SomeTable
    SELECT IDNumber, SomeColumns
    FROM WholeDataSet
    WHILE IDNumber BETWEEN @iMIN AND iMAX
END TRY
--If the insert fails, try the insert 1 row at a time until you find the error.
BEGIN CATCH
    BEGIN TRY
        --Loop through entire subset of data
        WHILE @i < @iMAX
            --Increment counter
            SET @i = @i + 1
            
            --Insert 1 row
            INSERT INTO SomeTable
            SELECT IDNumber, SomeColumns
            FROM WholeDataSet
            WHERE IDNumber = @i
        END WHILE
    END TRY
    BEGIN CATCH
        SELECT 'Error at row' + CONVERT(VARCHAR, IDNumber)
        FROM WholeDataSet
        WHERE IDNumber = @i
    END CATCH
END CATCH

Open in new window

T HoecherlDeveloper

Author

Commented:
Yes, acperkins, I just realized that as I was experimenting using OUTPUT to try to save a record of what had been inserted.  So would you agree with viralypatel that a cursor is the only option?

Can I use something like @@ROWCOUNT  or @@IDENTITY to at least give me the identity column value of the row that caused the error?
Top Expert 2012

Commented:
>>So would you agree with viralypatel that a cursor is the only option?<<
Only if you don't mind waiting an eternity.
did you test with the corsor in table variable? It will be surprisingly faster than a normal cursor because it won't do a physical read every time the cursor loops.