What is the best way to insert / update / delete multiple records in a database from an application?

Question

Given a small set of objects (say, 10 or less) to insert, delete, or update in an application, what is the best way to perform the necessary database operations? Should multiple requests be issued, one for each object to be affected? Or should you use any XML construct that can be parsed by the database engine, so only one command needs to be issued?

I ask this because the common template in my current store seems to be to format the XML document containing all the changes, and then send this line to the database, which will be processed by the XML database engine functionality. However, using XML in this way seems rather cumbersome, given the simple nature of the task.

Answer 1

It depends on how much you need to do and how quickly you need to complete the operations. If these are just a few, then doing them one at a time with any mechanism that you have to perform individual operations will work fine.

, , , . . .

Answer 2

, , SQL Server 2008 , , . . . .

Answer 3

BULK UPDATE BULK DELETE.

Answer 4

"-", , , .

(, " , x " ), , . .

Answer 5

, . , , , , . , .

Recently, I had to implement a mechanism in which an external data feed gave me ~ 17,000 dta rows, which I needed to synchronize with the local table. The solution I chose was to load external data into the staging table and invoke a stored process that fully synchronized in the database.