Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 476
  • Last Modified:

Insert error message and data associated into another table

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
pvsbandi
Asked:
pvsbandi
  • 6
  • 4
1 Solution
 
knightEknightCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
pvsbandiAuthor Commented:
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
knightEknightCommented:
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
 
pvsbandiAuthor Commented:
:))))

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
 
knightEknightCommented:
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
 
knightEknightCommented:
>> 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
 
pvsbandiAuthor Commented:
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
 
knightEknightCommented:
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
 
knightEknightCommented:
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
 
pvsbandiAuthor Commented:
Thanks so much!
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now