Bulk Insert/Update in MS SQL

added by ebizdom
6/7/2012 8:54:20 AM

3 Kicks, 484 Views

This article explains how to insert/update bulk data in .net application. This way your application will improve performance. For example, inserting few hundred records together into same table, we normally do something like:For i AsInteger = 0 To 200'SQL insert/updateNext For insert, Luckily, .NET has a SqlBulkCopyClass that Lets you efficiently bulk load a SQL Server table with data from another source. Here, i will save datatable into sql table.Using bulkcopy As SqlBulkCopy = New SqlBulkCopy(Configur...


1 comments

dpeterson
6/7/2012 8:56:39 AM
A novel solution, another option would be to use the upsert method. In the upsert method, you attempt to update existing records first, and if the return code is 100, perform an insert. I'm not sure how it compares bulk copy, but with a couple hundred thousand rows on a decent size table an upsert in a loop without committing the transaction until the end shouldn't be too bad on performance either. I'd be interested to see some benchmarks between the three solutions.