Author Topic: After Bulkcopy success a question  (Read 122 times)

stanl

  • Pundit
  • *****
  • Posts: 668
After Bulkcopy success a question
« on: November 16, 2017, 03:32:52 am »
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

  • Tech Support
  • *****
  • Posts: 2223
    • WinBatch
Re: After Bulkcopy success a question
« Reply #1 on: November 16, 2017, 06:07:22 am »
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.
"Success is a lousy teacher. It seduces smart people into thinking they can't lose."
  - Bill Gates


JTaylor

  • Pundit
  • *****
  • Posts: 806
    • Data & Stuff Inc.
Re: After Bulkcopy success a question
« Reply #2 on: November 16, 2017, 06:32:52 am »
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

  • Pundit
  • *****
  • Posts: 668
Re: After Bulkcopy success a question
« Reply #3 on: November 17, 2017, 05:13:46 am »
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

  • Pundit
  • *****
  • Posts: 806
    • Data & Stuff Inc.
Re: After Bulkcopy success a question
« Reply #4 on: November 17, 2017, 05:30:03 am »
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

  • Pundit
  • *****
  • Posts: 806
    • Data & Stuff Inc.
Re: After Bulkcopy success a question
« Reply #5 on: November 17, 2017, 06:05:30 am »
Guess I found the right combination...went from about 12 hours to less than 5 minutes :)

Jim

JTaylor

  • Pundit
  • *****
  • Posts: 806
    • Data & Stuff Inc.
Re: After Bulkcopy success a question
« Reply #6 on: November 17, 2017, 07:14:34 am »
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

  • Tech Support
  • *****
  • Posts: 2223
    • WinBatch
Re: After Bulkcopy success a question
« Reply #7 on: November 17, 2017, 01:07:56 pm »
The top-secret solution has finally been revealed!   ::)
"Success is a lousy teacher. It seduces smart people into thinking they can't lose."
  - Bill Gates