Home Forums SQL Server 2008 T-SQL (SS2K8) Finding row number which caused the error in Table value parameter insertion RE: Finding row number which caused the error in Table value parameter insertion

  • September 2, 2013 at 9:28 pm

    #1646848

    Erland Sommarskog (9/2/2013)

    Unfortunately, this is one of the drawbacks with working set-based. If you insert rows one a time, finding the bad data is trivial. But that is of course far less efficient. A possible strategy is to trap the error with TRY-CATCH and resort to a loop in case of an error.

    There is very good Connect request for this problem on https://connect.microsoft.com/SQLServer/feedback/details/774754/new-virtual-table-errors-it-would-analogous-to-the-deleted-and-inserted-tables#details that you could vote for.

    Thanks for the reply Erland Sommarskog

    I voted that feature ...microsoft should add that feature.

    you said to use TRY-CATCH ...but the problem is any error happens it rollback all the transaction..i am not using any transaction may be it is using batch transaction which causing it rollback.

    Any workaround or i have to use loop to insert the data(Performance issue)