CODEWITHSUNDEEP
sqldatabasesql-server-2005database-connection
Saobi
Saobi

Reputation: 17021

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

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!

Upvotes: 0

Views: 4493

Answers (6)

Martin Olsen
Martin Olsen

Reputation: 1925

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

Upvotes: 3

marc_s
marc_s

Reputation: 754428

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

Marc

Upvotes: 0

Jacob
Jacob

Reputation: 78850

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.

Upvotes: 1

SpliFF
SpliFF

Reputation: 38966

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.

Upvotes: 0

joseph
joseph

Reputation: 29

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

Upvotes: 0

Jonathan Fingland
Jonathan Fingland

Reputation: 57167

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

Upvotes: 4

Related Questions