June 23, 2008 at 6:43 am
We import records from Excel into a SQL 20005 table.Fields are of type varchar(100) and float.
We import around 5 000 records at a time. No validation (other than to test for completeness) is required at import time and the table is used to generate a form in SQL reporting services.
Once this table has been imported I need to copy records into another sql database (same server). In the target table various validations are performed i.e. valid client code, no duplicates on certain fields etc.
The challenge is to enable error handling on a row level (with some kind of error code to give indication of error type).
We do not want to reject the whole batch that is inserted, but only the rows that fails insert.
At this stage it appears as if my only options are
a) loop through table with WHILE statement and build error handling on row level,
or b) Use cursor (with more or less same effect)or
c) Use Integration services (of which I have little knowledge).
I am cautious of using cursors as it is clear that it has performance issues when scaling large amounts of records. I am not sure if a normal loop will perform any better than a cursor.
Any advice on best practice to bulk insert from one SQL table into another SQL table with error handling will be appreciated.
June 23, 2008 at 7:10 am
It will depend on the types of errors you are looking for. Here is what I like to do:
Define an Error Description table that includes some kind of error description (or FK to one) and an FK to the staging table.
Define a group of stored procedures that find errors and insert records into the error table. For Example:
[font="Courier New"]INSERT ErrorTable (RecordID, ErrorID, ErrorDate)
SELECT RecordID, 1, GETDATE()
FROM StagingTable
WHERE NameField IS NULL OR NameField = ''[/font]
Then, in the actual insert of the data, LEFT JOIN to the error table and only include records with an ErrorID that is NULL (records with no errors).
Finally, delete the records that have been inserted so the error records remain in the staging area to be dealt with.
This approach will mean running several queries to insert error records rather than doing it in one operation, but you also get the benefit of it being modular - you can add a new error insert procedure to handle a new situation pretty easily.
June 23, 2008 at 7:55 am
Thank you very much. I think this approach will work. The only disadvantage will be that I will have to test for each possible error type (i.e. duplicates, nulls etc) specifically before insert. Obvious if my insert procedure fails I then will know that I have missed an error type.
June 23, 2008 at 8:03 am
Yes. Sometimes, I group some of the checks together - checking for NULL in any columns, for example. You lose the detail of which columns are a problem, but in some types of errors that amount of detail is not always necessary.
Also, if you are using SSIS, checks like this can be done in parallel by putting the calls into individual Execute SQL components.
June 23, 2008 at 9:15 am
Thanks, I will give it a go.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply