SQLBulkCopy vs DataAdapter for inserting/update 10lcs records

Question

I have around 1,000,000+ records that I want to insert into the database.

Records having same Id should be updated else inserted

update/insert logic is written in Stored Proc

I'm using DataAdapter.Update method to do this work.

Here is the problem.

A DataTable takes not more than 65,000 records.

Insert/update is only 60 records per second.

How can I improve this performance, and can i use SqlBulkCopy here, can it do this Update/Insert together? what would be the performance in it

possible duplicate of [Using SQLBulkCopy to Insert/Update database](http://stackoverflow.com/questions/1700487/using-sqlbulkcopy-to-insert-update-database) — Tony Hopkinson, Sep 19 '12 at 17:05
possible duplicate of [Any way to SQLBulkCopy "insert or update if exists"?](http://stackoverflow.com/questions/4889123/any-way-to-sqlbulkcopy-insert-or-update-if-exists) — StingyJack, Sep 19 '12 at 17:20
This is two separate questions. The first duplicate Tony mentions addresses the performance, and the one I mention covers the "can I use SQL Bulk Copy to do inser/update?" — StingyJack, Sep 19 '12 at 17:22
Rather then ask SQLBulkCopy vs DataAdapter describe the format of the source data and ask how to improve load speed. — paparazzo, Sep 19 '12 at 17:22

score 0 · Answer 1 · edited May 23 '17 at 11:49

0

Are you loading the records into a DataTable just for the insert update?

If so you would probably be better off just calling the SP directly one at time and skip the DataTable.

Or you could use TVP and IEnumerable SqlDataRecord and then you are not limited to 65000.

edited May 23 '17 at 11:49

Community

answered Sep 19 '12 at 17:09

paparazzo

1 Answers1