Quote Originally Posted by szlamany View Post
I've always used Bulk Insert myself when presented with a large file - OP said that might not be possible.

I've never used SQLBulkCopy - how does it compare to a Bulk Insert?
Obviously since there is some processing on the client, and the data has to be sent to the server... it's not as quick as doing a pure BCP ... But it is definitely far superior to doing row by row inserts. Any time I do file imports now, I always reach for the SQLBulkCopy ... I don't even consider anything else. The more I work with it, the more about its capabilies I learn - for instance the mapping... until I learned about that a couple months ago, I thought the client datatable had to mirror EXACTLY what the destination table looked like... turns out that's not the case and you can setup mapping so that you can skip cols if you need to, or as in the case specifically I had to setup, the source table had two cols with the same header name, but their stating table had different names... so with the mapping, it was possible to map "Col1" to "Column1" and the other "Col1" to "Column2" ... it also gives flexibility in the naming of the fields to comply with out platform SDK rules, while still retaining the flexibility of hte format for the files (which often come to our clients from other vendors who charge every time you request a change to their output.

I've been processing some sample files with approx 1000 records in them, 12 fields... it imports in about 3 seconds, that includes opening the file, building the temp datatable, sucking the data in, minor processing and then sending it off to the SQLBulkCopy. We have other bottlenecks in the system unrelated to the BCP so I don't usually pay attention to it much... I'm going to be running some tests later tonight, I can give some numbers on the data... I don't have a ready made BCP I can compare it to, so I'm not sure it'll be a whole lot useful, but now I'm curious about the performance of it, so I'll crank out some ridiculously large files for it to process and see what happens.

-tg