SQL SERVER – How to Catch Errors While Inserting Values in Table

Question: “I often get errors when I insert values into a table, I want to gracefully catch them, how do I do that.”

Answer: Very simple. Just use TRY… CATCH. Here is the simple example of TRY…CATCH I have blogged earlier when it was introduced.

Here is the example, I have build from the earlier blog post where user can catch the error details during inserting value in table.

First, we will create a sample table.

CREATE TABLE SampleTable (ID INT IDENTITY(1,1), Col VARCHAR(10))
GO

Now we will attempt to insert value in this table which will throw errors and the same error we will catch into the table.

BEGIN TRY
INSERT INTO SampleTable (Col)
SELECT 'FourthRow'
UNION ALL
SELECT 'FifthRow---------'
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO

The second row of the above table will throw an error as the length of the row is larger than the column in which we are inserting values. It will throw an error and the same error will be caught via TRY…CATCH and it will be displayed in the SELECT statement. Here is the result set.

SQL SERVER - How to Catch Errors While Inserting Values in Table error8152

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – How to Find Running Total in SQL Server
Next Post
MySQL – How to Create a Distributed Relational SQL Database

Related Posts

6 Comments. Leave new

  • Can we get more specific info like, which row or column generated the error, like I have case where I have 79 columns and I am porting data from one table to another table.

    Reply
  • Pinal, I liked your quick shots of solutions to problems we encounter in Sql Server queries.

    Can you help us capture failed rows in a different stream, and allow the remaining good rows to be inserted fine?

    Reply
  • how can I insert this error message in a log table??

    Reply
  • I think, you have to write insert statement within the catch block.

    Reply
  • Pinal, is there any way we can skip the error record and resume with the next insert statement? Let me know. thank you!!

    Reply

Leave a Reply

Menu