After Bulkcopy success a question

[stanl]

In the .net SQLBulkcopy class is an event called SQLRowsCopied.  It is fired by another event called SQLNotifyAfter.  So if NotifyAfter =1000 the rows copied event is fired every 1000 inserts. My recollection is that the WB CLR does not work with events, so this may be a moot point - but I would like to see if I could get a display of the rows copied after the operation is complete. Since I cannot test this directly as I can only test with compiled exe's I want to avoid a lot of back and forth. So I hope to get some suggestions on how I might use the event or not.

[td]

WIL CLR hosting does not support delegates so it does not support events.  And no that support is not likely to happen any time in the foreseeable future for technical reasons.

[JTaylor]

If there is a serial field could you do a "select Max(field)" at appropriate times and get what you want?  It would assume you are not doing any IGNORE or REPLACE actions.   

Jim

[stanl]

If there is a serial field could you do a "select Max(field)" at appropriate times and get what you want?  It would assume you are not doing any IGNORE or REPLACE actions.   

Jim

Not sure that would work well with bulkcopy. I did discover that setting a moderate batch size [so there are no memory problems] has been successful when I manually go over results. I'm going to suck it up - meaning the script is incredibly fast [loaded 30,000 rows in around 3 seconds] and I have procedures to inspect on the back end. Besides, this is just a mock-up [my local SQLEXPRESS] design for the big boys to look at in the real corporate server(s).

[JTaylor]

Hope you find a good solution.  I have actually been working with bulk loads in MySQL.  Still trying to find a fast way to do this.  I find posts of people saying they load a million records in times like 2.6 seconds but my times for 6.6 million often takes many, many hours so not sure what is different.   Just found some more info on suggestions so getting ready to try that.


Jim

[JTaylor]

Guess I found the right combination...went from about 12 hours to less than 5 minutes :)

Jim

[JTaylor]

I found that it helps the speed A LOT if you mess up the SQL so that you are not actually loading anything.

Jim

[td]

The top-secret solution has finally been revealed!   ::)