Solved

Insert error message and data associated into another table

Posted on 2013-12-05
11
456 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
Comment Utility
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
Comment Utility
>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
Comment Utility
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
Comment Utility
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
Comment Utility
:))))

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 33

Expert Comment

by:knightEknight
Comment Utility
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
Comment Utility
>> 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks so much!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

772 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

15 Experts available now in Live!

Get 1:1 Help Now