?
Solved

Insert error message and data associated into another table

Posted on 2013-12-05
11
Medium Priority
?
469 Views
Last Modified: 2013-12-06
Hi,

I have this SQL as an example. The third insert fails with the error message "Conversion failed when converting the varchar value 'ABC' to data type int.".

Now, i want to insert 'ABC' and the above error message into ##ERROR_TABLE.
How can i do it?

CREATE TABLE ##ABC(A INTEGER);
CREATE TABLE ##ERROR_TABLE(B VARCHAR(1000));
INSERT INTO ##ABC VALUES (111);
INSERT INTO ##ABC VALUES (222);
INSERT INTO ##ABC VALUES ('ABC');

Open in new window

0
Comment
Question by:pvsbandi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 39699795
CREATE TABLE ##ABC(A INTEGER);
CREATE TABLE ##ERROR_TABLE(B VARCHAR(1000));

  BEGIN TRY
    INSERT INTO ##ABC VALUES (111);
    INSERT INTO ##ABC VALUES (222);
    INSERT INTO ##ABC VALUES ('ABC');
  END TRY

  BEGIN CATCH

     insert into ##ERROR_TABLE select ERROR_MESSAGE()

  END CATCH

Open in new window

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39699819
>CREATE TABLE ##ABC(A INTEGER);
>INSERT INTO ##ABC VALUES ('ABC');
You can't insert the string 'ABC' into the integer column A.  
'banana', 'coral', and 'wackadoo' won't work either.

If your intent is to allow it, then use
CREATE TABLE ##ABC(A varchar(100))

>INSERT INTO ##ABC VALUES (111);
As an aside, it's considered good programming practice to explicitly define all columns in an insert.

INSERT INTO ##ABC (A) VALUES (111)
0
 

Author Comment

by:pvsbandi
ID: 39699901
Thanks for your input. Sorry, i forgot to add another column to the Error table.
It should be defined as
CREATE TABLE ##ERROR_TABLE(A INT,B VARCHAR(1000));
and should store the values 'ABC' and 'Conversion failed when converting the varchar value 'ABC' to data type int.."'.

Inserting the error message is achieved, but not the value because of which it erred out.

Please help.
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 33

Expert Comment

by:knightEknight
ID: 39699950
In the error table, A can't be an int because it won't insert as an int into this table any better than it would in the other.  :)



CREATE TABLE ##ERROR_TABLE(A varchar(30),B VARCHAR(1000));

...

  BEGIN CATCH

     insert into ##ERROR_TABLE select 'ABC', ERROR_MESSAGE()

  END CATCH
0
 

Author Comment

by:pvsbandi
ID: 39700023
:))))

KnightEKnight,

    the very reason why i was inserting a character string for an Integer column, is for it to throw an error :)) and so, can be captured.

   So, i would like to capture what value failed while inserting, which is 'ABC' in my example and the other column to capture the error message.

I hope i'm not confusing you further.
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 39700064
Yes, but in your example you defined column A as an int in the error table, and it won't hold 'ABC', so I changed it to a varchar(30).  Then it is just a matter of adding the value into the insert statement of the error table.  In my most recent example I hard-coded this value, but in your real scenario you would use the actual source of the value.
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 39700066
>> the very reason why i was inserting a character string for an Integer column, is for it to throw an error ...

Yes, this made sense to me in the ABC table, but it won't work as an int in the ERROR table either.  :)
0
 

Author Comment

by:pvsbandi
ID: 39700937
Thanks i understood your post now!..

>> In my most recent example I hard-coded this value, but in your real scenario you would use the actual source of the value.

but again, the question is, how to reference the value that is causing it to fail? How do we capture that value?
0
 
LVL 33

Accepted Solution

by:
knightEknight earned 2000 total points
ID: 39700979
One way is to use a local variable to hold your insert data (since it appears you are inserting one row at a time)

CREATE TABLE ##ABC(A INTEGER);
CREATE TABLE ##ERROR_TABLE(A varchar(30), B VARCHAR(1000));

declare @data varchar(30)

BEGIN TRY

  select @data = '111';
  INSERT INTO ##ABC VALUES (@data);

  select @data = '222';
  INSERT INTO ##ABC VALUES (@data);

  select @data = 'ABC';
  INSERT INTO ##ABC VALUES (@data);

END TRY

BEGIN CATCH
  insert into ##ERROR_TABLE select @data, ERROR_MESSAGE()
END CATCH

GO

if exists(select 1 from ##ERROR_TABLE) 
  select * from ##ERROR_TABLE
else
  print 'No errors encountered.'

GO

truncate table ##ABC
truncate table ##ERROR_TABLE

drop table ##ABC
drop table ##ERROR_TABLE

Open in new window

0
 
LVL 33

Expert Comment

by:knightEknight
ID: 39701012
Not sure what your larger context is, but you can determine ahead of time whether a value will fail or not by using the ISNUMERIC function:

if ISNUMERIC('123') = 1
   insert into ##ABC '123'
else
  insert into ##ERROR_TABLE  ...


Or, more appropriately, if you are working with data from a table, this will exclude all the rows that are not numeric:

  insert into ##ABC
  select myDataColumn
  from myTable
  where ISNUMERIC(myDataColumn) = 1
0
 

Author Closing Comment

by:pvsbandi
ID: 39701086
Thanks so much!
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question