January 14, 2009 at 1:05 pm
I have a table stg.SRC_BRANCH
All the columns are varchar(255)
My goal is before inserting it into stg.STG_BRANCH
(data types enforced)
to find the records that will fail conversion.
Something like:
BEGIN TRY
select cast(transit_num as datetime) from stg.SRC_BRANCH
END TRY
BEGIN CATCH
update stg.SRC_BRANCH set DQ_DC = '3' where .... ?
END CATCH
I know I can use SSIS data conversion task
and redirect failed records. But I am trying to build something reusable, like:
exec s_Convert @tableName = 'stg.SRC_BRANCH'
If some records fail conversion I don't want to abort the whole transaction.
I still want to be able to convert records that are OK.
January 14, 2009 at 1:08 pm
I've done that kind of thing before, and I usually use checks like IsDate or IsNumeric to validate the data before I try to run an insert on it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 15, 2009 at 2:10 pm
I was planning to do the same thing.
But how about INT or SMALLINT, for example?
There is no such thing as IsSmallint()
I tried to do it in a function
but without TRY/CATCH I don't know how to implement it
and function does not allow TRY/CATCH.
Maybe IsSmallint() should do these checks:
1. IsNumeric()
2. Check if cast(@x as smallint) greater than -32768 and cast(@x as smallint) less than 32768
but if CAST() fails that's it.
Script fails.
Really. The only solution seemes to be TRY/CATCH. Maybe inside stor proc?...
My goal is to have a function for every Data Type:
...where IsDate(x) = 0
...where IsBit(x) = 0
...where IsSmallint(x) = 0
...where IsBigint(x) = 0
...where IsFloat(x) = 0
...where IsMoney(x) = 0
etc....
January 15, 2009 at 2:23 pm
First, check for IsNumeric. On the rows that pass that test, check the range with a Between statement.
Run each test as a series of checks. Rows that pass all tests, or can be corrected by some simple update, are then passed to the next process, which inserts them into the target table. Rows that fail any test at all are inserted into a "problems" table, preferably with something that indicates which test(s) they failed.
At my last job, I had a very complex import process, from Excel into SQL Server, and the data was provided by monkeys, or at least looked that way sometimes. The proc that pre-validated the data was quite complex, but each piece of it was very simple.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 16, 2009 at 7:02 am
GSquared,
Thank you for a good idea!
BETWEEN, of course! That's what will help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply