SQLBulkCopy CLass - For Updates?

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

Hello,

I am writing a batch program for inserts and updates. I have done a POC
for the inserts and all is well. However, I also need to update a second
table , but only one column. Is it possible to use SQlBulkCopy for that?
If so, how? (I havent found any examples online).

Thanks in advance,
Ben
 
Ben said:
I am writing a batch program for inserts and updates. I have done a POC
for the inserts and all is well. However, I also need to update a second
table , but only one column. Is it possible to use SQlBulkCopy for that?
If so, how? (I havent found any examples online).
Click to expand...
No. SqlBulkCopy can only do bulk inserts. You could, however, bulk copy a
(temporary) table containing the primary key of the table you need to update
and the update column value, and use a single UPDATE statement to update
them all.
 
Thanks.

Just to reiterate - your recommendation is to have "Work table", have
SQLBulkCopy populate that (id and update columns) , and then call a proc
to update the actual table in the end (and perhaps truncate the work
table afterwards?)
 
Ben said:
Just to reiterate - your recommendation is to have "Work table", have
SQLBulkCopy populate that (id and update columns) , and then call a proc
to update the actual table in the end (and perhaps truncate the work
table afterwards?)
Click to expand...
I'd drop the table rather than truncating it, but that's the general idea.
And it is an idea, not a recommendation. Whether it's the best thing to do
in your scenario depends.

For one thing, a mass UPDATE produces far more overhead in the transaction
log than a BULK INSERT (though using the TABLOCK hint can mitigate this), so
it might not be acceptable compared to lots of "regular" UPDATE statements.

Another consideration is that in order to bulk copy the updates, you must
first determine them client-side. If the updated values can be completely
determined in the database, though (for example, foreign key lookups), it's
far more efficient to have the database do it, as you save network roundtrips.
 
Thanks.

Just to reiterate - your recommendation is to have "Work table", have
SQLBulkCopy populate that (id and update columns) , and then call a proc
to update the actual table in the end (and perhaps truncate the work
table afterwards?)

*** Sent via Developersdexhttp://www.developersdex.com***
Click to expand...

Hi,

Just take into account that the timeout of the SP can be reached if
you import a big chunk of data. You need to test it to the limit y ou
plan to hit
 
Back
Top