Multiple Insets - Bulk Insert or Multiple Proc Calls?

[RobS23]

A quick 'opinions' question.

Returning between 2 and 50 records from a dataset to a SQL table. What is the prefered methodolgy: a for...each loop repeatedly calling a stored procedure and passing a record at a time or utilise some of the bulk insert methods.

Like to know your opinions guys.

 

[rsinj]

for such a small amount of data just use the loop/sp

 

[john76]

I suppose it depends on your needs. Looping through the set gives you more power over verifying the data, whereas a bulk insert does not.

Looping through the set is also slower than bulk insert, but with 2-50 records I don't think you would notice the difference.

John

 

[donutman]

I'll assume that "by returning" means inserting. I think the "correct" answer may be dependant on the programming language. It would also depend on how you get the 2 to 50 rows of data. Obviously if they are already in a txt file, then a bulk insert is the way to go. Otherwise bulk is hardly 50!
Under most circumstances I would suggest a simple SP that inserts a row. If VB, then create a command object for the SP, refresh the parameter object and loop away.
-Karl

 

[RobS23]

Thanks Guy's.

Exactly what I thought - sometimes reading around this forum just sows the seeds of doubt!