3

Can anybody tell me how to bulk insert data into SQL Server under a transaction? I have a program to import data into multiple tables of a database from a file. I am using SQL Server and Entity Framework to import those records into the database under a transaction. Please tell me is it good way to follow?

I am using Entity Framework for this. But when I import 1000 records, I found it causes locks on SQL Server.

The file contains many columns. Few columns belongs to parent table and the rest of columns belongs to child tables. So how could we map them using SqlBulkCopy?

There are 10 child tables. I need to insert data from file to child tables first. If insertion of records cause error in child table for some what reason I will have to rollback that transaction and continue with another record from file

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sonu
  • 458
  • 5
  • 13
  • Entity Framework is not ideal for bulk inserts for reasons of **speed** - `SqlBulkCopy` will be much faster. **BUT** even the bulk copy component **will use** locks on your database table! No way around that! – marc_s Jan 25 '14 at 09:17

2 Answers2

2

Today i found a good library for bulk insert: EntityFrameworkETL

PM> Install-Package EntityFrameworkETL

Project Description Entity Framework ETL is used to batch commands using an existing DbContext. This is useful when moving data between production and development.

Example Usage

ETL = new EntityFrameworkETL.ETL(() => new DataContext("name=source"), () => new DataContext("name=target"));
ETL.DeleteAll<Person>();
ETL.BatchInserts<Person>(100, true, x => x.Include("Jobs").Where(y=> y.Age > 65 && y.IsRetired));
  • Please give some description about use and its capability so that i can use it efficiently. – Sonu Jan 25 '14 at 06:54
  • This entityframeworketl.codeplex.com does not have documentation yet – Sonu Jan 25 '14 at 07:11
  • Same line of code is available on codeplex too. But It doesn't match my actual requirement. As i wrote for each row of file i need to insert the data in child table first by selecting few columns in row. Then if the record inserted successfully in child table then i need to insert few coulumns of row in parent table. – Sonu Jan 25 '14 at 07:29
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/46060/discussion-between-sonu-and-shahrooz-jefri-) – Sonu Jan 25 '14 at 07:31
  • And if any things goes wrong we must rollback. And continue with the next record. – Sonu Jan 25 '14 at 07:34
  • Crap, now I find this. I rolled my own version of this. Works pretty well, BTW (despite the fact that it's massively chatty). – dudeNumber4 Mar 03 '14 at 19:34
0

This type of operation would work best as a stored procedure. Otherwise you will multiple back SQL calls.

Basically you create a stored procedure that accepts a user-defined type, and operate on passed data.

This post has some info on how to do this.

Community
  • 1
  • 1
Charles Graham
  • 1,157
  • 5
  • 6