Pushing inserts in SQL Server using C #?

Question

I want to insert about 5,000 (usually maybe 10K + at times) rows in SQL Server at a time. My insert statements are generated by the C # program.

One way could be to simply create a large string full of these SQL statements and then actually execute SQL Server using SqlCommand.ExecuteNonQuery();. But there is a risk that SQL Server might not execute such a large statement.

Is there a way I could push SQL statements to some “SQL Server buffer” (if any), and then finally execute them all?

EDIT - I work with SQL Server 2005, 2008. So, I need something compatible with both.

Thanks.

Answer 1

Use SqlBulkCopy to achieve this. I think this will be the best approach.