0

I have millions of rows to insert in SQL Server, but some of them are in error, I would like to know if there is a way for the INSERT to continue even after the error? and know which lines are in trouble?

I tried to do it this way, but it didn't work.

CREATE TABLE DB_Errors (
    ErrorID INT IDENTITY(1, 1)
    , UserName VARCHAR(100)
    , ErrorNumber INT
    , ErrorState INT
    , ErrorSeverity INT
    , ErrorLine INT
    , ErrorMessage VARCHAR(MAX)
    , ErrorDateTime DATETIME
    );
    
GO

CREATE TABLE TEST (
    ID INT
    , NAME VARCHAR(10)
    );
    
GO


BEGIN TRY 

    INSERT INTO TEST (ID, [NAME]) VALUES (1, 'aaa') ;
    INSERT INTO TEST (ID, [NAME]) VALUES (2, bbb);  -------- Error
    INSERT INTO TEST (ID, [NAME]) VALUES (3, 'ccc');
    INSERT INTO TEST (ID, [NAME]) VALUES (4, 'ccc');
    INSERT INTO TEST (ID, [NAME]) VALUES (5, 'ccc');

END TRY
BEGIN CATCH

    INSERT INTO dbo.DB_Errors
    VALUES (
        SUSER_SNAME()
        , ERROR_NUMBER()
        , ERROR_STATE()
        , ERROR_SEVERITY()
        , ERROR_LINE()
        , ERROR_MESSAGE()
        , GETDATE()
        );

END CATCH 

GO

Thanks.

5
  • What is the error you're getting? What version of SQL Server?
    – J.D.
    Oct 27, 2022 at 12:42
  • You would need to wrap each insert statement in it's own try/catch block to do what you are doing. What is the source of the data? If it's a flat file, then PowerShell/SSIS should provide a more error tolerant method for inserting that kind of bulk data. Oct 27, 2022 at 12:47
  • If you remove the try/catch, errors will be reported (same info you are inserting into the error table) and the script will continue. If you need to log each error to a table, you'll need each insert wrapped in a try/catch like @JonathanFite suggested, with each catch block inserting into the error table.
    – Dan Guzman
    Oct 27, 2022 at 13:51
  • I'm using SQL Server 2019 I'm using a .sql file but you need to do it in the management studio.
    – Carlos D
    Oct 27, 2022 at 17:19
  • How could I do this with Power Shell?
    – Carlos D
    Oct 27, 2022 at 17:31

2 Answers 2

2

If you have "millions of rows to insert", then using individual INSERT statements for each row is not the right way of doing it.

Consider using the bulk load utility bcp, which is specifically designed for this task. It has the -e option that tells it to record rejected rows in a separate file.

0

You can edit multiple lines at once in SSMS (and some other applications) using the alt trick.

You hold the alt key and drag your cursor over the lines.

SSMS Alt Trick Demo

This should let you wrap the TRY/CATCH around each of the lines more easily.

If that's going to be unmanageable because of the number of lines you could use SSMS to find and replace (CTRL-H) and enable regex mode (.* button - highlighted in yellow in the gif) to replace the line breaks like this:

find: \r - regex line break

replace: END TRY BEGIN CATCH INSERT INTO dbo.DB_Errors VALUES (SUSER_SNAME(), ERROR_NUMBER(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_LINE(), ERROR_MESSAGE(), GETDATE()); END CATCH\rBEGIN TRY

This will leave you with one extra BEGIN TRY at the very bottom, and a missing one at the very top, you can cut the extra one and paste it at the top to cover the missing one. (if you have other line breaks select the text set the mode to selection instead of current document) enter image description here

Good luck!

1
  • Thank you Patrick. it worked here
    – Carlos D
    Nov 18, 2022 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.