kselva
kselva

Reputation: 343

If network connection is lost while inserting large amount of data, what will happen?

I am using ASP.NET with C# and SQL Server. I am inserting 10000 records sequentially.

Assume that after inserting 6000 records, the network connection is lost. At this time what will happen? Will the the 6000 records be inserted or not?

If those 6000 records are inserted - what about the remaining 4000 records, and how to identify how many records are inserted and how to reverse 6000 records?

Is there any other option to rectify this problem? If this problem occurred what do I need to to to solve it? There should be no duplicate data in the end.

Upvotes: 1

Views: 130

Answers (1)

TomTom
TomTom

Reputation: 62127

It depends what you consider inserting.

10.000 insert statements, no transaction: nothing is rolled back.

10k insert statements in a transaction - rollback of all operations.

BEST way is to plan for that:

  • Use SQLBUlkCopy into a temporary table, then isnert from there into the final table (SQlBulkCopy has some serious issues with concurrent table access that you bypass like this).
  • Use a MERGE to move into the final table.

Plan for rerunning the insert - Mergfe makes sure of that (no cleanup needed).

Upvotes: 2

Related Questions