0

I am looking for an efficient way to perform inserts of multiple rows with some values from multiple tables!

Basically I'm parsing an XML and creating a list of custom objects which I then want to insert to my database. Some of the values of these rows are contained in the XML but others need to be retrieved from other tables.

Example:

Two tables:

Person: 
  PersonID (identity), Name (nvarchar), CountryID

Country (a lookup table including my id and the one I get from the XML) 
  CountryID (identity) , CountryXMLID, Name

I go through my list in C#, create a list of Teams(custom objects) and then I want to do this for an average of 1000 rows per minute:

INSERT INTO Person (Name, CountryID)
SELECT 'George', CountryID FROM Countries WHERE CountryXMLID = '1';

Person name and country feed id are included in the XML. I want to use the ID in the Person table rather than the one in the XML..

Could someone please advice me on an efficient manner to perform these inserts.

I have been thinking about creating a DataTable and then using SQLBulkCopy, using a stored procedure to run a loop through the datatable and perform the above statement for each one, and ORM like EntityFramework, Telerik Data Access, etc..

Serpiton
  • 3,676
  • 3
  • 24
  • 35
diabolo
  • 21
  • 4
  • Have you looked at Table Valued Parameters? – dyson Jul 18 '14 at 10:08
  • thank u for ur quick reply.. yes i was thinking of creating a datatable with the parameters i get from the xml and submitting it to a stored procedure that accepts Table Valued Parameters. That would run through a loop and perform the above statement for each iteration.. would that be efficient? – diabolo Jul 18 '14 at 10:15
  • For high performance, use C#'s [`SqlBulkCopy`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(v=vs.110).aspx) class – Andomar Jul 18 '14 at 10:18
  • from what i've read up until now i agree with Andomar that SQLBulkCopy is a high performance way to do it but what about the retrieval of the values from other tables? – diabolo Jul 18 '14 at 10:23
  • It might be more efficient to SqlBulkCopy to a separate staging table, then just doing a single insert with a select: `INSERT INTO Person (Name, CountryID) SELECT s.Name, c.CountryID FROM PersonStage s join Countries c on s.CountryXMLID = c.CountryXMLID` – Rhumborl Jul 18 '14 at 10:23
  • XQuery http://msdn.microsoft.com/en-us/library/ms189075.aspx – podiluska Jul 18 '14 at 10:25
  • Could you explain what you mean by ' I want to use the ID in the Person table rather than the one in the XML.' – Paddy Jul 18 '14 at 12:33
  • I also would agree with the table value parameter. There is no need to do any looping in sql. It is just like any other table at that point. You would do a single insert statement in your procedure. – Sean Lange Jul 18 '14 at 13:26
  • @Paddy I mean i have a lookup table that i want to use in order to use my own id rather than the one given in the XML – diabolo Jul 19 '14 at 14:16

0 Answers0