Solved

Insert error message and data associated into another table

Posted on 2013-12-05
11
458 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
  • 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 65

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
 
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now