February 13, 2008 at 9:23 pm
Guys, I have a major problem! I have these table which I import from a flat file to SQL table in SQL database. However, my job is to convert the datatypes that is coming in from a different format to the one that my DBA has already assigned. Problem is, I have to point out 4 possibilities of errors(if any), and then send it to another table called Success_Failure. If there is no errors, it should send back the file "Success" to the sender which I have done. I am only stuck with the error part only.
I have to check about 50 columns on each 4 tables and return any errors to the Success_Failure table
The 4 errors that I must find are:
1. Missing Data(Nulls where it should be Not Null)
2.Invalid Datatype(varchar(50) when it suppose to be numeric38, 0)
3.Length(75 characters when it should only be 50)
4.Invalid date(1986 instead of 1986-12-3-00-00-00)
How do I do this?! I have no idea at all? Can someone help with a complete description. A good set of SQL statement will help a bunch!
Thanking you guys in advance!
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
February 16, 2008 at 7:01 pm
Could you post an example to explain what you want to do?
--------------------------------------------------------------
DBA or SQL Programmer? Who Knows. :unsure:
February 18, 2008 at 2:21 am
I'm not sure I want to spend an hour or so writing your code. However, this is the way I'd go about it.
Import the flat file into a table using BCP or whatever,
Create four extra columns. (bit or int)
in one update statement, do your data sanity-checking, putting a value in one of the extra columns according to whether the data column conforms. (eg case when Mycol is null then 1 else 0 end).
This way you only have to the error-checking logic once (in real life this can get quite complicated)
Then all your bad data can be cleaned out and put in other tables accordingly, just using SELECTs and DELETEs. You won't need a cursor for any of this.
Best wishes,
Phil Factor
February 18, 2008 at 10:43 pm
Thanks so much for that idea. I tried it out and it worked. I am also trying something easier. If I ever succeed in that, I will share it. But I really appreciate the help. Thanks for helping out. Go SQL Server Central !!!;)
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply