I had an interesting flashback today. Someone wrote with a reference to a mailing list posting I'd written in 2000, about IRowsetFastLoad. They wanted code, because nowadays attachments to public postings have become a thing of the past. Looked in my OLE DB code, from back when I was teaching "Essential OLE DB", and … voila. (I know there's a code sample in SQL BOL, and told the letter writer about it, but finding the original code was way more fun. Written in ATL and all, too.
The poster wanted to know what I'd recommend instead, if this didn't work out (his inserts are too slow). Choices that came to mind were:
1. BULK INSERT SQL statement
2. BCP API in C – it's ODBC-based IIRC
3. SqlBulkCopy class from ADO.NET 2.0
There's something for choices #1 and #3 in the new SQL Server 2005/ADO.NET 2.0 stuff. Of course SqlBulkCopy (misnamed, it's it actually more like IRowsetFastLoad) is new in ADO.NET 2.0. And BULK INSERT has been enhanaced/rewritten as a "rowset provider", an OLE DB-like provider whose name is "BULK". The enhancements are more functional than speed-based. I haven't done speed tests (it's too early) but someone posted on newsgroups in Beta 1 that "BULK" was actually slower than the old BULK INSERT. It's supposed to be as fast by RTM. The new BULK supported XML inserts also, include multiple XML documents separated by "row separator characters". Think CSV files of XML data type. Whew…
This caused me to wonder if the new SQL Native Client OLE DB provider (SQLNCLI) supports IRowsetFastLoad too. Hmmm….says in BOL that there is not only IRowsetFastLoad but an IBCPSession interface on the new SQLNCLI provider. Maybe something for choice #2 also.