Search code examples
.netsql-server-2008.net-4.0insertbulk

Best way to insert multiple rows into SQL Server 2008 database from .NET 4.0 app

I have a scenario where I get back a list of items and I need to insert each item into a database, with one item > one row mapping.

Initially, I thought about using a stored procedure that would insert a single item, and looping over a list, calling the stored procedure for each element. But I wonder if there are more elegant/optimal methods available. Especially leveraging SQL Server 2008 / .NET 4.0.

Entity Framework could also come into play.

Solution

  • SQLBulkCopy is quick and easy, you can set up a batch size and map all of your columns

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx