0

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.

Improve this question
3
  • I would actually look at this other Pinal Dave article (blog.sqlauthority.com/2013/04/18/… ) that talks about using SSIS for this sort of thing. It will give you the flexibility you need and will give you error handling and really you can essentially have it do whatever you want. And it is fast. Certainly worth looking at if you are comfortable with checking out that environment.
    – mskinner
    Sep 1, 2015 at 20:31
  • And just substitute his SalesForce destination for a SQL Server one. The same principles will work.
    – mskinner
    Sep 1, 2015 at 20:32
  • BULK INSERT into a temporary table, which you can trash if something seems wrong. Then BULK INSERT from the temp table into the target table when everything is all right.
    – mustaccio
    Sep 2, 2015 at 2:03

1 Answer 1

Reset to default
0

BULK INSERT into VARCHAR(MAX) in my opinion, then you shouldn't have errors and from there you can manipulate the data as needed and run standard SQL to make sure it fits within your requirements before actually placing it in the destination table. Just a suggestion but it's how I've done it in the past.

Improve this answer
3
  • 2
    I would only use this for very irregular data where I could not confidently pre-define the results to go into the needed columns. Otherwise, creating a table that matches the data definition is much, much more reliable.
    – RLF
    Sep 2, 2015 at 1:44
  • Agreed RLF. OP stated "I tend not to trust the user input" so that is why I suggested this option.
    – Josh Simar
    Sep 2, 2015 at 18:10
  • I decided, and tested, this alternative which I'll be using at least during the the testing period. I'll definitely try to improve the way I get to the result... Thanks!
    – Nelson
    Sep 4, 2015 at 18:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.