3

I'm running SQL files with sqlcmd which insert many rows into an MS SQL server (2008 R2), doing BULK INSERT would be optimal, but the data files might not be reachable for the SQL server.

Which other ways or options is there to do (very) fast inserts on on MS SQL server 2008 R2?

Qtax
  • 33,241
  • 9
  • 83
  • 121
  • 2
    possible duplicate of [Alternative to SQL BULK INSERT](http://stackoverflow.com/questions/579406/alternative-to-sql-bulk-insert) – Woot4Moo May 27 '11 at 20:46

2 Answers2

2

Ended up with writing a custom C# application that uses System.Data.SqlClient.SqlBulkCopy. Also replacing the SQL files containing the data with XML files generated by System.Data.DataTable.WriteXml, which could later be imported with ReadXml and be directly used with SqlBulkCopy.

This solution is about 10 times faster than using plain INSERTs, and 50 times faster than using inserts with 1000 rows each. (Why inserts with many rows values per statement is so much slower than insert statements with one row each is a mystery to me.)

Qtax
  • 33,241
  • 9
  • 83
  • 121
1

You could also use the bcp utility. I've found it to be faster & easier to use than the BULK INSERT statement. The only caveat is that you may need to run it on the server itself & put your files in a shared directory accessible by the server. Not too sure if you really need to do that.

floydopedia
  • 31
  • 3
  • `bcp` looked good, but I didn't find anything on how it handles field/row separators that are present in the data, binary data, nulls. So DataTables WriteXml seems more robust in that way. – Qtax May 29 '11 at 14:44