February 15, 2012 at 3:55 am
Hi,
I have data in a temp table of data type VARCHAR(50), I need to insert this data into a table say TableA, of data type Float 1. Need to check data conversion errors
2. Arithmetic overflow errors.
By ignoring this errors, need to insert (using Merge) data into TableA , without stopping the batch and need to raise error message one time to the UI How to handle this?
February 15, 2012 at 5:22 am
It can't be done exactly that way. A DML statement is executed completely or not executed at all.
However, you could check the values in advance and import valid rows only.
-- Gianluca Sartori
February 15, 2012 at 9:54 am
Can't you use parameter tables, a WHILE loop, and a TRY..CATCH block to accomplish this? Perhaps a couple tables like @Records (for the main data to be loaded into) and a @Errors (for the bad records).
Create a loop (while @RecCount <> 0) and then put the TRY...CATCH within the loop? Insertions would occur in the BEGIN TRY... and if it errored out the CATCH could insert the row into the @Errors table. This should allow it to run until all records have been processed, then you could go back to the @Errors table to see which ones failed.
Just a thought though...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 15, 2012 at 10:12 am
You can also use ISNUMERIC and case to input only valid numeric or null when its invalid.
Depending of your tables and needs you can also log or create a log column/table to hold the bad data for further analysis.
February 15, 2012 at 10:18 am
jcb (2/15/2012)
You can also use ISNUMERIC and case to input only valid numeric or null when its invalid.Depending of your tables and needs you can also log or create a log column/table to hold the bad data for further analysis.
Well, ISNUMERIC = 1 does not always guarantee you're dealing with a proper number, but I second this approach.
-- Gianluca Sartori
February 15, 2012 at 10:40 am
ISNUMERIC is not perfect since chars like .+- can fool it.
But if you use like it:
ISNUMERIC(value + 'e0')
ill improve correctness a lot.
February 15, 2012 at 10:42 am
Use SSIS. It will allow you to simply ignore conversion errors, and even log them if you wish to
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 15, 2012 at 10:16 pm
jcb (2/15/2012)
ISNUMERIC is not perfect since chars like .+- can fool it.But if you use like it:
ISNUMERIC(value + 'e0')
ill improve correctness a lot.
In this case, the improvement of "correctness" may be too much. Looking back at the original post, the target column is a float which means that SIGN characters, decimal points, "E" (engineering notation) and "D" (decimal notation) should be allowed. Your method would reject all of those. Remember that being "numeric" doesn't mean "is all digits".
--Jeff Moden
Change is inevitable... Change for the better is not.
February 15, 2012 at 10:23 pm
Gianluca Sartori (2/15/2012)
However, you could check the values in advance and import valid rows only.
Heh... WHAT a concept!! 🙂 Actually checking data instead of throwing it against the wall to see what sticks! You may call yourself the "Spaghetti DBA" but you definitely don't write "Spaghetti code".
Matt Miller has another good idea, as well.
My idea would be to seriously porkchop the data provider if there are any such data errors and, perhaps, have a serious reevaluation of why I'm trying to store such data in a VARCHAR(50) column to begin with.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2012 at 3:09 am
Jeff Moden (2/15/2012)
jcb (2/15/2012)
ISNUMERIC is not perfect since chars like .+- can fool it.But if you use like it:
ISNUMERIC(value + 'e0')
ill improve correctness a lot.
In this case, the improvement of "correctness" may be too much. Looking back at the original post, the target column is a float which means that SIGN characters, decimal points, "E" (engineering notation) and "D" (decimal notation) should be allowed. Your method would reject all of those. Remember that being "numeric" doesn't mean "is all digits".
Jeff, my point is:
select isnumeric('.'+'e0'),ISNUMERIC(','+'e0'),ISNUMERIC('+'+'e0'),ISNUMERIC('-'+'e0'),ISNUMERIC('0e0'+'e0')
is better than
select isnumeric('.'),ISNUMERIC(','),ISNUMERIC('+'),ISNUMERIC('-'),ISNUMERIC('0e0')
and these are the most common "bad data" when converting from a string to float.
As I said, unfortunately isnumeric is not perfect, someone can ask MS to implement a C# float.tryparse like function to do the trick (and it can be done with sp_addextendedproc anyway).
It depends largely on the data, if there are some pattern you can write a regular expression to check the data.
Personaly, to do this task I can create a stag table, clean the data, check the data and only send valid data to the production tables.
February 16, 2012 at 3:23 am
jcb (2/16/2012)
As I said, unfortunately isnumeric is not perfect, someone can ask MS to implement a C# float.tryparse like function to do the trick (and it can be done with sp_addextendedproc anyway).
Great idea! In fact, TRY_PARSE will be included in SQL Server 2012. 🙂
-- Gianluca Sartori
February 16, 2012 at 4:29 am
I would suggest ETL-like approach. The following is shown as an example:
-- table setup:
-- tS is a staging table where you load raw data
-- tA is a distination table
CREATE TABLE tS (id int identity(1,1), val varchar(1024))
CREATE TABLE tA (val FLOAT)
-- some test data:
insert tS values ('12345'),(replicate('9',1024)),('1231abc'),('9989')
--ETL
-- 1. apply validation and insert results into error table
-- it doesn't need to be #-table, but you are better to use "select into"
select id, dbo.udf_ValidateFloat(val) as error
into #error
from tS
-- 2. You can insert valid records
insert tA (val)
select val
from tS s
join #error e
on e.Id = s.Id
where e.error is null
-- 3. you can return errors if any
select * from #error where error is not null
Now the function...
It whould be cool if you could do something like:
create function udf_ValidateFloat( @val varchar(1024) )
returns varchar(1024)
as
begin
declare @error varchar(1024)
declare @flval float
begin try
set @flval = cast(@val as float)
end try
begin catch
set @error = error_message()
end catch
return @error
end
But! You cannot use TRY-CATCH in UDF. But even if you could I would not recommend doing it as it would not perform good enough for large datasets.
The following is provided just for example to be able to run the setup I've included above.
create function udf_ValidateFloat( @val varchar(1024) )
returns varchar(1024)
as
begin
declare @error varchar(1024)
if isnumeric(@val) != 1 set @error = 'Value is not numeric'
return @error
end
The above CAN NOT BE used to properly validate convertability of strings to float or any numeric datatypes! It will allow to tun the setup to show how it works.
If you go with this solution you MUST implement the validation function as CLR function.
There you will be able to do proper validation and it will give you the best performance.
Please note: If you want to use this aproach you must write CLR-function
February 16, 2012 at 8:21 am
Going out on a limb... Do you have the ability to stop the data from being stored as a VARCHAR in the temp table and actually use the type that it is? Then have the application validate the data to the user before inserting into the temp table. Sounds like the better way to do it, "If you can change it." 🙂
Jared
CE - Microsoft
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply