Flat File validation of 20+ fields

Question

Flat File validation of 20+ fields

tom.sage 70179

SSC Veteran

Points: 255
More actions
January 1, 2014 at 8:42 am

#280851

I am using SQL 2008 R2. I am importing from Flat Files, and I need to validate 20+ fields in each row to insure they are numeric.
I have created code shown below to validate each field.
UPDATE ValidateTable Set in_value = ''
OUTPUT deleted.*, 'Non Numeric in_value', GETDATE(),@CustomerID INTO ErrorTable
Where ISNUMERIC(in_value) = 0;
Is there a faster way to do this validation besides having 20 separate field tests?
Thanks
Tom

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You could use an errorfile during the import operation to validate your values. Be sure to have an acceptable value for MAXERRORS to avoid the import to fail.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

tom.sage 70179 SSC Veteran Points: 255 More actions · Answer 2

Hi Luis,

Thanks for your reply. Are you talking about using BCP to import the flat file using the error file option? If not, can you give me more information?

Thanks

Tom

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Yes and no.

All import utilities from SQL Server (bcp, bulk insert, openrowset, SSIS) have options to handle errors. The first 3 have the errorfile and max errors options. SSIS has the error output option to redirect rows to a different destination instead of making the component fail.

The question would be what are you using right now.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

tom.sage 70179 SSC Veteran Points: 255 More actions · Answer 4

I am using BCP to import the Flat file into a Table with ALL fields set to varchar(255) so there will be no validation errors during loading. Next, I am using a separate T-SQL commands like the one shown above to validate each field. This does work, but I would like for it to be faster.

Perhaps I should use SSIS and compare the speed to what I have now?

Thanks

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 5

tom.sage 70179 (1/1/2014)
...into a Table with ALL fields set to varchar(255) so there will be no validation errors during loading

THAT would be the primary problem. From the sounds of it, you're already importing into a staging table and it would take little time to make to define the columns as the correct datatype that you're expecting. What Luis suggested about MAXERRORS and ERRORFILE is spot on.

Perhaps I should use SSIS and compare the speed to what I have now?

No. SSIS won't be any faster and could be a good bit slower if you continue to import to VARCHAR(255) and then do "datatype checking" separately. Let the table work for you. Define it correctly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tom.sage 70179 SSC Veteran Points: 255 More actions · Answer 6

Hi Jeff,

Thanks for the good advice. I will change the "staging table" to have the same field types as the production table. Then I will check for errors from the BCP import.

Thanks

Tom