I've seen a couple of questions about the subject backfiring so I'll try to be specific about my concern...
I have an old (MS Access) frontend I'm trying to rebuild and I left the part that troubles me most for last, so here I am. The task is to 'grab' a file (used to be an XLS), now it would more that likely be by-product of it, say CSV for the sake of the example.
So this is my first steppingstone, I'm concerned about the result of my import, according to Pinal Dave
BULK INSERT is a non atomic operation:
If there is any error in any row it will be not inserted but other rows will be inserted.
This is not appealing to me since I tend not to trust the user input, I want more control thus being able to trigger an error and not importing any part of the file would be better. I'm not sure if this effectively wipes the possibility of using
BULK INSERT for this process?
I also tried reading line by line and inserting using a
LOOP cycle, this is effective but unfortunately its takes to long even after optimizing my code I just went from 15 minutes to 4, and that's only for 850 records.
I'm a bit mad that the process worked so smoothly when running from the Access frontend. That was because of the
DoCmd.TransferSpreadsheet VBA method. This allowed for fast XLS import into a temp table and from that one the execution carried on updating tables.
I would like to know what other options have guys tried when importing data from flat file sources into SQL Server. Admittedly I'm a bit confused at this point.
Any help would be appreciated.