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
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.