Search code examples
sql-server-2008sqlcmd

What's the fastest way to INSERT a lot of rows whithout using BULK INSERT?

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?

Solution

  • 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.)