Bulk Insert from Generic List into SQL Server with minimum lines of code

Entity Framework and even classic ADO.NET is very slow when you need to perform a large number of inserts into the database.

Calling 1000 times INSERT INTO is not efficient because for every INSERT operation, SQL Server will also need to write to the transaction log.

A nice workaround for this is to use Bulk insert or SQLBulkCopy class in C#.

However SQLBulkCopy method WriteToServer does not accept List<T> or IEnumerable, IList as parameter.

Implement extension method AsDataTable for IEnumerable<T>

A nice workaround is to implement extension method AsDataTable to IEnumerable because WriteToServer accepts DataTable as parameter.

Here is a simple implementation to do this:

public static class IEnumerableExtensions    {        public static DataTable AsDataTable<T>(this IEnumerable<T> data)        {            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));            var table = new DataTable();            foreach (PropertyDescriptor prop in properties)                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);            foreach (T item in data)            {                DataRow row = table.NewRow();                foreach (PropertyDescriptor prop in properties)                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;                table.Rows.Add(row);            }            return table;        }    }

After this we can use this extension method to Bulk insert data from List<T> into SQL Server.

var listPerson = new List<Person>     {          new Person() {Id = 1},          new Person() {Id = 2}     };   using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SomeConnectionString"].ConnectionString))     {          connection.Open();          SqlTransaction transaction = connection.BeginTransaction();            using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))          {             bulkCopy.BatchSize = 100;             bulkCopy.DestinationTableName = "dbo.Person";             try             {                 bulkCopy.WriteToServer(listPerson.AsDataTable());             }             catch (Exception)             {                 transaction.Rollback();                 connection.Close();             }           }             transaction.Commit();     }

Optimal BatchSize depends on the number of items you need to insert, row size, network bandwidth and latency.

After doing some testing I can recommend BatchSize of 100 when you need to insert few thousand items into the database.

The best way to find optimal BatchSize value is to experiment yourself.

Code first - Cannot insert the value NULL into column 'Id'

public Category(string name, string urlName, int index)
{
    CategoryId = Guid.NewGuid();
    Name = name;
    UrlName = urlName;
    CategoryIndex = index;
}

Solution

[Key]
[DatabaseGenerated(DatabaseGenerationOption.None)]
public Int64 PolicyID { get; set; }

MERGE (Transact-SQL)

In SQL Server 2008, you can perform multiple data manipulation language (DML) operations in a single statement by using the MERGE statement. For example, you may need to synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table. Typically, this is done by executing a stored procedure or batch that contains individual INSERT, UPDATE, and DELETE statements. However, this means that the data in both the source and target tables are evaluated and processed multiple times; at least once for each statement.

By using the MERGE statement, you can replace the individual DML statements with a single statement. This can improve query performance because the operations are performed within a single statement, therefore, minimizing the number of times the data in the source and target tables are processed. However, performance gains depend on having correct indexes, joins, and other considerations in place

USE [MyDatabase]
GO
merge into mytable as Target
using mytable2 as Source
on Target.id=Source.id
when matched then 
update set Target.name=Source.name,
Target.Salary = Source.Salary
when not matched then
insert (id,name,salary) values (Source.id,Source.name,Source.Salary);

Bulk Load Best Practices

The MERGE statement can be used to efficiently bulk load data from a source data file into a target table by specifying the OPENROWSET(BULK…) clause as the table source. By doing so, the entire file is processed in a single batch.

To improve the performance of the bulk merge process, we recommend the following guidelines:


Create a clustered index on the join columns in the target table.


Use the ORDER and UNIQUE hints in the OPENROWSET(BULK…) clause to specify how the source data file is sorted.

By default, the bulk operation assumes the data file is unordered. Therefore, it is important that the source data is sorted according to the clustered index on the target table and that the ORDER hint is used to indicate the order so that the query optimizer can generate a more efficient query plan. Hints are validated at runtime; if the data stream does not conform to the specified hints, an error is raised.




These guidelines ensure that the join keys are unique and the sort order of the data in the source file matches the target table. Query performance is improved because additional sort operations are not necessary and unnecessary data copies are not required. The following example uses the MERGE statement to bulk load data from StockData.txt, a flat file, into the target table dbo.Stock. By defining a primary key constraint onStockName in the target table, a clustered index is created on the column used to join with the source data. The ORDER and UNIQUE hints are applied to the Stock column in the data source, which maps to the clustered index key column in the target table.

Before running this example, create a text file named 'StockData.txt' in the folder C:\SQLFiles\. The file should have two columns of data separated by a comma
USE AdventureWorks2008R2;
GO
CREATE TABLE dbo.Stock (StockName nvarchar(50) PRIMARY KEY, Qty int CHECK (Qty > 0));
GO
MERGE dbo.Stock AS s
USING OPENROWSET (
    BULK 'C:\SQLFiles\StockData.txt',
    FORMATFILE = 'C:\SQLFiles\BulkloadFormatFile.xml',
    ROWS_PER_BATCH = 15000,
    ORDER (Stock) UNIQUE) AS b
ON s.StockName = b.Stock
WHEN MATCHED AND (Qty + Delta = 0) THEN DELETE
WHEN MATCHED THEN UPDATE SET Qty += Delta
WHEN NOT MATCHED THEN INSERT VALUES (Stock, Delta);
GO

reference:
 https://msdn.microsoft.com/en-us/library/cc879317.aspx
https://technet.microsoft.com/en-us/library/bb510625.aspx
http://stackoverflow.com/questions/17967374/sql-merge-statement-not-working-in-stored-procedure
http://www.databasejournal.com/features/mssql/article.php/3739131/UPSERT-Functionality-in-SQL-Server-2008.htm