"Simon Harvey" wrote:

> Hi All,
>
> I'm not sure how to approach this problem so if anyone could advise me
> how to do it I would be very grateful.
>
> I have an app that calls a web service. The webservice returns an array
> of business objects.
>
> So I have a collections of objects that might be, say, 10000 objects in
> size.
>
> I need to import all the business objects into an SQL Server 2005
> database. The only way I know how to do this is to loop through each
> object and call a stored procedure repeatedly that takes the appropriate
> parameters. The whole import must be atomic. That is, if something goes
> wrong, the whole import needs to be aborted.
>
> I guess I have two questions:
>
> 1. Is repeatedly calling a stored procedure the best way to do this sort
> of stuff? I know doing imports like this must be a pretty common
> operation, but I don't know if using an SPROC is a dumb idea.
>
> 2. Is it ok to start a transaction and repeatedly call a SPROC, like,
> 10000 times? Is there a better what to go about this?
>
> Maybe I'm worrying about nothing - but it just "feels" wrong to be
> repeatedly calling an SPROC 10,000 times! :-) I was wondering if there
> is a more elegant way?
>
> Any advice anyone could offer would be very much appreciated.
>
> Kindest Regards
>
> Simon
>

"Simon Harvey" <notha***@hotmail.com> wrote in message
news:ezy9dt4mGHA.732@TK2MSFTNGP04.phx.gbl...
> Hi All,
>
> I'm not sure how to approach this problem so if anyone could advise me how
> to do it I would be very grateful.
>
> I have an app that calls a web service. The webservice returns an array of
> business objects.
>
> So I have a collections of objects that might be, say, 10000 objects in
> size.
>
> I need to import all the business objects into an SQL Server 2005
> database. The only way I know how to do this is to loop through each
> object and call a stored procedure repeatedly that takes the appropriate
> parameters. The whole import must be atomic. That is, if something goes
> wrong, the whole import needs to be aborted.
>
> I guess I have two questions:
>
> 1. Is repeatedly calling a stored procedure the best way to do this sort
> of stuff? I know doing imports like this must be a pretty common
> operation, but I don't know if using an SPROC is a dumb idea.
>
> 2. Is it ok to start a transaction and repeatedly call a SPROC, like,
> 10000 times? Is there a better what to go about this?
>
> Maybe I'm worrying about nothing - but it just "feels" wrong to be
> repeatedly calling an SPROC 10,000 times! :-) I was wondering if there is
> a more elegant way?
>
> Any advice anyone could offer would be very much appreciated.
>
> Kindest Regards
>
> Simon

"Arnie Rowland" <ar***@1568.com> wrote in message
news:eyYZQd5mGHA.1204@TK2MSFTNGP03.phx.gbl...
> You're right. You don't want to call the same stored procedure 10,000
> times if there is way to accomplish the task as one unit of work. First
> off, calling the stored procedure 10,000 times -while inside a
> transaction, will consume and hold more resources longer than necessary.
>
> Look into SQLBulkCopy as Alejandro offered
>
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
>
> *Yet Another Certification Exam
>
>
> "Simon Harvey" <notha***@hotmail.com> wrote in message
> news:ezy9dt4mGHA.732@TK2MSFTNGP04.phx.gbl...
>> Hi All,
>>
>> I'm not sure how to approach this problem so if anyone could advise me
>> how to do it I would be very grateful.
>>
>> I have an app that calls a web service. The webservice returns an array
>> of business objects.
>>
>> So I have a collections of objects that might be, say, 10000 objects in
>> size.
>>
>> I need to import all the business objects into an SQL Server 2005
>> database. The only way I know how to do this is to loop through each
>> object and call a stored procedure repeatedly that takes the appropriate
>> parameters. The whole import must be atomic. That is, if something goes
>> wrong, the whole import needs to be aborted.
>>
>> I guess I have two questions:
>>
>> 1. Is repeatedly calling a stored procedure the best way to do this sort
>> of stuff? I know doing imports like this must be a pretty common
>> operation, but I don't know if using an SPROC is a dumb idea.
>>
>> 2. Is it ok to start a transaction and repeatedly call a SPROC, like,
>> 10000 times? Is there a better what to go about this?
>>
>> Maybe I'm worrying about nothing - but it just "feels" wrong to be
>> repeatedly calling an SPROC 10,000 times! :-) I was wondering if there is
>> a more elegant way?
>>
>> Any advice anyone could offer would be very much appreciated.
>>
>> Kindest Regards
>>
>> Simon
>
>

> If your data is coming in as one large XML document, XML Bulk Load might be
> another alternative.  The final result is the same as SQLBulkCopy but the
> input is an XML document.
>