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.

T HoecherlDeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

viralypatelCommented:
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.
0
T HoecherlDeveloperAuthor 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.
0
viralypatelCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Anthony PerkinsCommented:
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.
0
SwindleCommented:
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

0
T HoecherlDeveloperAuthor 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?
0
Anthony PerkinsCommented:
>>So would you agree with viralypatel that a cursor is the only option?<<
Only if you don't mind waiting an eternity.
0
viralypatelCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.