Batch Updates

Can somebody from Microsoft suggest a way to do batch updates from a stored
procedure ? I was considering OPENXML but it has a lot of memory related
problems so i need something where i can update/insert multiple rows without
making round trips to the server. Thanks!
Michael,
I already have the stored procedures to update the tables. Is there anyway i
can map the xml directly to stored proc parameters using transformation ?
Thanks!
"Michael Rys [MSFT]" wrote:

> If the XML is not very big the memory issues of OpenXML should not be
> playing a big role.
> You can however do one of the following for better perf:
> 1. Use the SQLXML Bulkload object.
> 2. Write some midtier code that decomposes your XML into either one of:
> a Bulkload input file
> b. A batch of T-SQL insert and update statements.
> HTH
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:5818E367-1097-497F-9BD2-BACDE859450E@.microsoft.com...
>
>
|||You will have to write mid-tier code to do so, I am afraid...
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:39F52718-9899-4F67-BC91-4D6CF00415E4@.microsoft.com...[vbcol=seagreen]
> Michael,
> I already have the stored procedures to update the tables. Is there anyway
> i
> can map the xml directly to stored proc parameters using transformation ?
> Thanks!
> "Michael Rys [MSFT]" wrote:
|||Michael,
I know thats the easy way out but is there any other approach for sending
batch data to a stored procedure ? Looping in the application can be done but
if i need to make it in a transaction is it a better idea to have the
transaction in the sproc or the outside in the code itself ?
Thanks!
"Michael Rys [MSFT]" wrote:

> You will have to write mid-tier code to do so, I am afraid...
> Best regards
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:39F52718-9899-4F67-BC91-4D6CF00415E4@.microsoft.com...
>
>
|||I don't think there is much of a difference whether you send multiple
invocations of the same sproc with different parameter values in a single
batch that is sent as a transaction or a stored proc that does it inside.
Again, there may be a benefit of using OpenXML inside a stored proc to do
your update expressions set-oriented under certain conditions instead of
generating your batch on the midtier and send a large batch to the server.
The easiest is to write a sample app to test the approaches with some of the
data to be used and using the same setup (to get the client-server comm cost
right).
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:9F88DE36-35AA-4E6B-9DC5-B2EBD82CACF6@.microsoft.com...[vbcol=seagreen]
> Michael,
> I know thats the easy way out but is there any other approach for sending
> batch data to a stored procedure ? Looping in the application can be done
> but
> if i need to make it in a transaction is it a better idea to have the
> transaction in the sproc or the outside in the code itself ?
> Thanks!
> "Michael Rys [MSFT]" wrote:
|||Did you look into the general Bulkload and BCP functionalities?
Is the data originally in XML or do you just consider XML as a potential
approach?
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:CEF7CBCC-3AEE-4062-B491-560CF56729DC@.microsoft.com...
> Can somebody from Microsoft suggest a way to do batch updates from a
> stored
> procedure ? I was considering OPENXML but it has a lot of memory related
> problems so i need something where i can update/insert multiple rows
> without
> making round trips to the server. Thanks!
|||Michael,
The data is originally in xml. I am doing realtime updates to the tables and
the xml is not very big. Moreover i have to span the updates in a
transaction. If there is a different approach than xml i can transform it to
a different format.
Thanks!
"Michael Rys [MSFT]" wrote:

> Did you look into the general Bulkload and BCP functionalities?
> Is the data originally in XML or do you just consider XML as a potential
> approach?
> Best regards
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:CEF7CBCC-3AEE-4062-B491-560CF56729DC@.microsoft.com...
>
>
|||If the XML is not very big the memory issues of OpenXML should not be
playing a big role.
You can however do one of the following for better perf:
1. Use the SQLXML Bulkload object.
2. Write some midtier code that decomposes your XML into either one of:
a Bulkload input file
b. A batch of T-SQL insert and update statements.
HTH
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:5818E367-1097-497F-9BD2-BACDE859450E@.microsoft.com...[vbcol=seagreen]
> Michael,
> The data is originally in xml. I am doing realtime updates to the tables
> and
> the xml is not very big. Moreover i have to span the updates in a
> transaction. If there is a different approach than xml i can transform it
> to
> a different format.
> Thanks!
> "Michael Rys [MSFT]" wrote: