Importing lots of rows :-)

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
SimonSimon,
Try using SqlBulkCopy object (ado.net 2.0).
AMB
"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
>|||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" <nothanks@.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.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Arnie Rowland" <arnie@.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" <nothanks@.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
>|||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
Roger Wolter[MSFT] wrote:
> 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.
>

Importing Data??

I have a process that calls a proc that BCP's a delimited file into a table. Well the SOX police say a header and footer must be added to the file. Needless to say this screws my BCP process.

Does anyone know how to strip a header and footer record from a text file using transact sql or have any other suggestions to strip the records?Well, -F will allow you to bypass the headers... If you can magically devine the number of rows, then -L will allow you to bypass the footers.

If that doesn't suffice, you can always use brute force! Either specify a format file and a batch size of 1 row (really ugly performance), or BCP into a staging table, then only copy the rows of interest from the staging table to the production table.

The other option that will get the job done, but might infuriate the SOX-meisters would be to use the "Flintstone" method... Write one utility to apply the requisite header and footer, and a separate utility to remove the header and footer before using BCP to import the data!

-PatP|||Yeah, well...I'm sure SOX is gonna ask you to interogate thos headers and trailers as well...

bcp the whole damn thing in to a single column table varchar(8000)

Sounds like a mainframe file, so do you have record identifiers?

Like 'H', 'D', and 'T'?|||Yeah, well...I'm sure SOX is gonna ask you to interogate thos headers and trailers as well...Oh! You're no fun!
bcp the whole damn thing in to a single column table varchar(8000)Nothing quite like brute force! 'Tain't pretty, but it does get the job done.

-PatP|||What's funny is they don't care if the header and footer are interogated. I'm lobbying to remove the footer since I can eliminate the header with -F.

My record/row delimiter is {CR}{LF}. I've decided my worse case senario is to add an extra column to the file and BCP the file into a staging table that populates the target table with all records where the new column is null. You know, populate the new column with the header and footer data and leave them blank for all other records. Still too much damn work for such a small problem.|||Let me ask you...what other tool could you possibly need in your toolbox, when GOD made this wonderful thing called a sledge hammer?

Peter,

The Header and trailer thing has always been a pain...

Add an IDENTITY column to get the last and first row...

(OK Pat, release the hounds...)

Do your intergoation and save the stats...

The use a simple bcp, with your first row = 2 and your last row = COUNT(*)

Then perform the audits...

I also like to do an INSERT with parsing to the final destination...but I like what I sketched out above better...

OK, now the discussion about how the data may not get loaded to the table in the same manner that it's in the file...

Take it away Pat...

(did I piss him off too?)|||Nah, it takes LOTS more than that to irrigate me!

I'm actually fine with that idea... The staging table was actually in my first posting, and an identity column makes processing easy. The only thing that might make it a booger to process would be variable length columns with delimiters, although SQL Server can handle that too (at some performance penalty relative to the way that BCP would handle it).

I'm for whatever works, and the less effort needed to get there, the better I like it! I've only got time for so much schtuff, and I really don't want to do any more than I have to!

-PatP