March 1, 2009 at 5:42 pm
I have done a lot of work with bulk insert recently. We are all familiar with BULK INSERT, SELECT INTO, and bcp.exe. BI will bulk insert to/from a flat file. SELECT INTO will bulk insert (minimally logged insert, actually), but only into a nonexistent table.
My work has been with bulk inserting into existing tables programmatically from an application. The data is in whatever format the application uses and comes from any source -- there are no flat files involved. This is usually at least 10X faster than using regular INSERT.
However, this is not an easy task. It can't even be done from ADO. Therefore, I am trying to gauge the interest in developing a marketable COM object BI utility that will provide simple ADO-like methods, and work with existing ADO objects that would make this easy for the application developer.
It would enable the developer to bulk insert data into any table and even bulk insert from one table to another. There would be no need for format files.
Is there already such an application available? Have I placed this in the correct forum?
Thank you for your help.
March 1, 2009 at 6:06 pm
Reasonman (3/1/2009)
However, this is not an easy task. It can't even be done from ADO. Therefore, I am trying to gauge the interest in developing a marketable COM object BI utility that will provide simple ADO-like methods, and work with existing ADO objects that would make this easy for the application developer.
From ADO? No. You do it in ADO.Net using the SQLBulkCopy class, which I have found to be fairly easy to use. Although, it is about a days' work the first time you do it to set everything up.
And I sincerely believe that the market for any COM object at this point in time, regardless of what it does, is close to zero.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 4, 2009 at 4:17 pm
From looking at SqlBulkCopy in BOL, it looks like it is designed primarliy to copy from one DB table to another.
I have written something much simpler. In ADO terms, you would call AddNew(), fill in your fields as usual, then call BulkInsert() instead of Update(). When you are done, you call Commit() to finish the bulk insert. Existing code that inserts one record at a time would require little modification.
The is useful in a situation where you have data in a proprietary, binary format or data coming in on a live feed (such as TCP/IP) and you want to create rows out of this data and insert them quickly into a DB.
It might be possible to create an IDataReader that would make this incoming data look like is comes from a standard data source, but it does not look like IDataReader was designed to do this.
As for COM, I know it is hated and considered obsolete. I could always put my object in a .Net wrapper. Doesn't most of .Net eventually end up in a COM object?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply