CODEWITHSUNDEEP
sql-servert-sqlerror-handlinginserttry-catch
XyFreeman
XyFreeman

Reputation: 67

Continue with INSERT after error, TSQL

I'm writing some exception/error handling in T-SQL.

The goal is to continue with an insert operation even if one of the inserts causes an error.

The current code looks something like this:

INSERT INTO targettable
   SELECT       
      *, GETDATE() 
  FROM      
      table_log l
  LEFT JOIN 
      someothertable m ON l.id = m.id
  WHERE 
      l.actiontype = 'insert'

The problem: sometimes, there is a faulty row/entry in the table table_log. This causes the entire insert operation to rollback. I want the server to continue with the insert operations after the error has occurred.

My ideas: I could use a cursor to handle each insert individually. But as far as I know, that would be horrible in terms of performance. I could also utilizes ignore_dup_key or XACT_ABORT OFF. But I strongly doubt that our DBA will allow that. Plus, I don't think that would be a good solution either.

Here is another idea:

DECLARE @rowcount int 

SET @rowcount = (SELECT COUNT(*) FROM table_log l WHERE actiontype = 'insert')


    BEGIN TRY
        WHILE @rowcount > 0
        BEGIN 

                looppoint:

                INSERT INTO target_table
                SELECT somecolumn, GETDATE() 
                    FROM        table_log l
                    LEFT JOIN   some_other_table m
                        ON  l.id = m.id
                    WHERE   l.actiontype = 'insert' AND
                            l.id NOT IN 
                            (SELECT id 
                            FROM noinsert_table)

                SET @rowcount = @rowcount -1

        END         
    END TRY
    BEGIN CATCH

        execute sp_some_error_catching_procedure

        INSERT INTO noinsert_table
        SELECT SCOPE_IDENTITY()

        SET @rowcount = @rowcount -1

        GOTO looppoint

    END CATCH

Basically, I want to catch the error-causing-row and use this information to exclude the row from the insert in the next loop. But I'm not sure if this will work, I don't think SCOPE_IDENTITY will give me the id of a failed row, just successful ones. Plus, this seems overly complex and prone to other problems.

If anyone has some tips, I'd gladly hear about it.

Upvotes: 0

Views: 2847

Answers (1)

HLGEM
HLGEM

Reputation: 96658

Why don't you clean the data before you insert it or as part of the insert select? That way you are only trying to insert data that you know will fit the parameters of the table you are inserting into. Then you can still use your set-based insert.

By the way, please never write code like that. You should always, always specify the columns in an insert in both the insert part of the statement and the select and never use select *. If someone rearranges the columns in a table, your insert will break or worse, not break but put the data into the wrong columns. If someone adds a column you don't need in the insert to the selected table, your insert will break. This sort of thing is a SQL antipattern.

Upvotes: 1

Related Questions