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.
Reference: Pinal Dave (https://blog.sqlauthority.com)
9 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.
Hey Hermant, did you find a solution for it?
Katie and Hemant, have you solved this scenario?
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?
how can I insert this error message in a log table??
I think, you have to write insert statement within the catch block.
Pinal, is there any way we can skip the error record and resume with the next insert statement? Let me know. thank you!!
i want to know how to know which table got the error. Would appreciate your reply on this Pinal. Thanks in advance.
Pinal, I have a check constraint for possible varchar values on one field. Is there a way to test a value if it is going to pass a check constrained before Insert? I do not want to list the possible values in code, if I do not have to, because when the constrained changes, I will also have to change the code. Thanks!