| Author |
Delete all and Insert many rows in SQL in one transaction
|
|
| Niklas E 2004-12-20, 8:46 pm |
| We receive a xml file containing a full set of a list of companies and their
phone numbers etc. What I would like to do with this data is to delete all
data in the tables and insert the latest full set of data. What I don't want
is to delete all data and then having some error so only part or nothing is
inserted because the information is crucial to other critical applications.
They can only handle a small delays so the file has to be split up into
smaller messages (one per company). Is there some way nice way to accomplish
this? We have schemas so the document is automatically split up into smaller
parts to be able to insert. Is it possible to use the orchestration to:
receive file
(begin transaction)
execute delete SP
divide file into smaller data parts which are inserted into DB (SP)
(commit transaction)
The problem for me seems to be how to divide the file in the orchestrion and
be able to hold the divided messages in the transaction? Would this be
possible? How? Will this lock the whole database?
Or is it safer to insert the data into a temp DB and then execute some DTS
at given times?
Best regards
Niklas E
| |
| Matt Meleski 2004-12-20, 8:46 pm |
| Do it all in one shot.
See Matt Miner's post below
http://www.m3technologypartners.com...72-d79cba73dcc2
Matt
"Niklas E" wrote:
> We receive a xml file containing a full set of a list of companies and their
> phone numbers etc. What I would like to do with this data is to delete all
> data in the tables and insert the latest full set of data. What I don't want
> is to delete all data and then having some error so only part or nothing is
> inserted because the information is crucial to other critical applications.
> They can only handle a small delays so the file has to be split up into
> smaller messages (one per company). Is there some way nice way to accomplish
> this? We have schemas so the document is automatically split up into smaller
> parts to be able to insert. Is it possible to use the orchestration to:
> receive file
> (begin transaction)
> execute delete SP
> divide file into smaller data parts which are inserted into DB (SP)
> (commit transaction)
>
> The problem for me seems to be how to divide the file in the orchestrion and
> be able to hold the divided messages in the transaction? Would this be
> possible? How? Will this lock the whole database?
>
> Or is it safer to insert the data into a temp DB and then execute some DTS
> at given times?
>
> Best regards
> Niklas E
>
>
>
| |
|
| I really recommend this approach. We use it on most data loading tasks.
Others we use the updategram but it can be really tricky to use if you want
to both update and create new rows in the same transaction.
"Matt Meleski" <MattMeleski@discussions.microsoft.com> wrote in message
news:35792926-F5FD-49C2-8454-9E7896B30468@microsoft.com...
> Do it all in one shot.
>
> See Matt Miner's post below
>
>
http://www.m3technologypartners.com...72-d79cba73dcc2[vbcol=seagreen]
>
> Matt
>
> "Niklas E" wrote:
>
their[vbcol=seagreen]
all[vbcol=seagreen]
want[vbcol=seagreen]
is[vbcol=seagreen]
applications.[vbcol=seagreen]
accomplish[vbcol=seagreen]
smaller[vbcol=seagreen]
and[vbcol=seagreen]
DTS[vbcol=seagreen]
| |
| Niklas E 2004-12-30, 7:50 am |
| Thanks alot. I will try that 
Best regards
Niklas E
"Xerox" <anon@anon.com> wrote in message
news:ONo$pM05EHA.2568@TK2MSFTNGP11.phx.gbl...
>I really recommend this approach. We use it on most data loading tasks.
> Others we use the updategram but it can be really tricky to use if you
> want
> to both update and create new rows in the same transaction.
>
> "Matt Meleski" <MattMeleski@discussions.microsoft.com> wrote in message
> news:35792926-F5FD-49C2-8454-9E7896B30468@microsoft.com...
> http://www.m3technologypartners.com...72-d79cba73dcc2
> their
> all
> want
> is
> applications.
> accomplish
> smaller
> and
> DTS
>
>
|
|
|
|