[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Insert error message and data associated into another table

Posted on 2013-12-05
11
Medium Priority
?
474 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

656 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