Using prepare and a loop will be fine for a very small number of rows, but it will not scale well. Using prepare is effectively the same as creating a stored procedure on the SQL Server that inserts one row at a time. The scalability issue is that each row requires a network round-trip between your application and the SQL Server; also auto-committing each row insert means SQL Server has to flush a log record to disk when each single row insert auto-commits.

For anything except a very small number of rows, you need to look at batching the inserts. This means submitting multiple rows (or all rows) in one or more steps to SQL Server. This results in many fewer network communications, and SQL Server can optimize the logging requirements, using a very fast bulk-load mode of operation.

There are multiple methods available, both within SQL Server (directly reading a text file, for example) and via .NET. SQL Server provides:

- The bcp utility http://msdn.microsoft.com/en-us/library/ms162802.aspx

- The BULK INSERT command http://msdn.microsoft.com/en-us/library/ms188365.aspx

- The OPENROWSET (BULK... command http://msdn.microsoft.com/en-us/library/ms190312.aspx

You can find an overview at http://msdn.microsoft.com/en-us/library/ms175937.aspx

From a .NET language connecting to SQL Server 2008, options for bulk/batch loading include:

- SqlBulkCopy http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

- Table-valued parameters http://msdn.microsoft.com/en-us/library/bb675163.aspx

You can find examples of using SqlBulkCopy and table-valued parameters all over the Internet. A couple of examples below:

http://orionseven.com/blog/2009/09/30/using-table-valued-parameters-in-sql-server-2008-and-c/

http://weblogs.sqlteam.com/mladenp/archive/2006/08/26/11368.aspx

http://www.sqlteam.com/article/use-sqlbulkcopy-to-quickly-load-data-from-your-client-to-sql-server

http://florianreischl.blogspot.co.nz/2009/11/table-valued-parametes-performance.html

As far as repeatable tests are concerned, you may find it sufficient to drop and recreate the destination table in SQL Server between each test. It is hard to argue that restarting SQL Server between tests is not a more thorough approach, however.