INSERT 2000 records into SQL Database all at a time from C# .NET code

Question

We need to INSERT 2000 records into SQL DB from C# .Net code. For this is there any way to INSERT all 2000 records at a time instead of executing the INSERT query for each record. Also how would be the performance impact of doing this?

Thanks & Regards Padma

Answer 1

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

Answer 2

you can do it quickly using SqlBulkCopy:

http://www.dotnetcurry.com/ShowArticle.aspx?ID=323

We use this to import a dataset of about 100,000 records into a SQL table in a few seconds, where 100,000 insert statements took 15 minutes.

Answer 3

There is no way you can do it in batch from C# code. 2000 rows will be quick to insert though.

One option may be available to you if you have the 2000 records in a flat file, you could copy it to the SQL server and create SP to BULK INSERT the file into a table. Then call the SP from your C# code. I would not bother unless you see having more rows than that and if you have huge target table.

Answer 4

Well, you could build a query that has multiple inserts in it:

string sql = "INSERT INTO table (col1, col2) values (@val1,@val2);" +
             "INSERT INTO table (col1, col2) values (@val3,@val4);"

But dealing with the parameters for the command would get a bit tricky.

You can also use DataAdapters and DataTables to do batch inserts. That would probably be the best option, although the usage is a bit strange (not nearly as nice as Java's addBatch() / executeBatch() for example).

Performance wise, you'll almost certainly get a nice performance boost by doing this, although you might need to play around with the batch size a bit to figure out what works best