Importing lots of rows :-)

[Simon Harvey]

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

 

[Guest]

Simon,

Try using SqlBulkCopy object (ado.net 2.0).


AMB

 

[Arnie Rowland]

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

 

[Roger Wolter[MSFT]]

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.

 

[Simon Harvey]

Thanks guys!

That XML one seems even more interesting actually, although I'm guessing
it might be quite hard to set up the mappings. Especially seeing as the
data is quite hierarchichal in nature.

Many thanks for your advice!

Simon