Most usual approach to multiple inserts and updates on database

Question

I would like to know what's the optimal approach to make a batch insert/update from CLR to SQL Server 2008 R2 database?

Optimal in terms of:

• code complexity - I don't want to over complicate my TSQL or CLR code to prove it can be done
• implementation simplicity
• speed - it should perform better than doing one-by-one processing

We usually read a batch of data but usually update one at a time. I would like to do this in batch manner as well. These days rich web applications make it simple to create/update a bunch of objects that we could easily commit to the database in one go. It's easy to get them to the server side in one go, but get them to DB is a different story.

Answer 1

Bulk insert is the way to go for you. Please refer to following link:

http://msdn.microsoft.com/en-us/library/ms188365.aspx