June 15, 2006 at 12:37 pm
Hi
I am loading a text file into different sql server tables, doing this through a store proc where in it reads the complete text file and put the data into tables.
Now..I want to validate each and every row of that data before loading into my tables, if the data does not meet the validation rule I shud not load the data and send an error mesg to the user saying that the text file contains wrong data at so and so place, so that the user has to correct that data in the text file and resend the text file.
How can I accomplish this through a single SP.
June 15, 2006 at 12:40 pm
insert textfile into temporary table. run check against temporary table. then insert data into the other tables you need to.
N 56°04'39.16"
E 12°55'05.25"
June 15, 2006 at 12:42 pm
>>How can I accomplish this through a single SP.
Why are you restricted to a single SP ?
This is the sort of task that a DTS package would typically be used for.
June 15, 2006 at 12:55 pm
I am able to do the same as you said using temp table. The only thing is how do i need to check for errors and notify the users regarding that so that then can do corrections and send a new text file.
June 15, 2006 at 1:06 pm
That's why I suggested DTS.
Load the text file to a staging/temp table. Give the table 1 extra column for an error code and default it to zero.
Run validation SQL task, update the error code column for any failing rows.
Run a SQL task that counts the number of records in the table with non-zero error code.
Use a DTS send email task to automatically email if the count of error records is non-zero.
June 16, 2006 at 8:05 am
PW..
when you say validation sql task, is it a store proc we need to write to validate.
June 16, 2006 at 8:39 am
As far as i think you can achieve all this in one sp itself.
First use bulk insert in your sp by building the dynamic sql
After the data is inserted run your validation, if it gets through then insert in main table else return with error to user.
------------
Prakash Sawant
http://psawant.blogspot.com
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply