Executing Multiple Insert Statements, what's the Best Way?

Question

I have a program in which I need to run multiple insert statements (dynamically generated) against a MS SQL Table. Right now, I do (pseudo-code):

Loop
Generate Insert Statement String
SQLCommand.Text = Generated Insert Statement String
SQLCommand.Execute().
End Loop

Close Connection

Is it better performance-wise to simply construct one big string of inserts, separated by semi-colons. And run only one SQLCommand.Execute() statement? Or does it not make a difference?

Thanks!

Answer 1

you might find this technique useful. It significantly cuts down on the number of statements processed.

Answer 2

it'll be a lot faster to batch them up, otherwise the sql server has to look at execution plans, etc. depending on how big the loop and the statements are i might end up sending N records at a time, and then letting it come back, just to give yourself some breathing room. the other thing that you might want to consider (i'm coming at this as if its 'throw away code', [one time thing]) is just doing a bulk load. SSIS is also an option...

Answer 3

On Postgres you would use COPY, which allows bulk inserts in a CSV-like format. Don't know if sqlserver has something similar.

Another way would be to use a stored proceedure and just pass a long list of data to some backend insert loop.

Answer 4

If you have the choice and ability to do so at this stage, you may want to upgrade to SQL Server 2008. It has a lot of added benefits, including an addition to T-SQL for doing multiple inserts in one statement.

Answer 5

Depends on just how many your multiple are. But you should definitely also look at Sql Bulk Copy - very fast, very handy.

• http://msdn.microsoft.com/en-us/library/7ek5da1a(VS.80).aspx
• http://www.codeproject.com/KB/database/SqlBulkCopy.aspx
• http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file
• http://dotnetslackers.com/articles/ado_net/SqlBulkCopy_in_ADO_NET_2_0.aspx

Marc

Answer 6

You need to start a transaction before you begin your inserts. Then, when you have send all your inserts to the server and finished the loop, you should commit. This will save you a lot writes on the database!

See more here: http://msdn.microsoft.com/en-us/library/5ha4240h.aspx