December 29, 2014 at 10:56 pm
Hi All,
I have 2 tables source & destination and I have to truncate destination and laod from source. these 2 stpes should happen in one transaction. but I should not use MSDTC transaction option and source & destination are different databases.
how can I acheive this?
Thanks in advance
December 30, 2014 at 3:15 am
sandhya.msbi (12/29/2014)
Hi All,I have 2 tables source & destination and I have to truncate destination and laod from source. these 2 stpes should happen in one transaction. but I should not use MSDTC transaction option and source & destination are different databases.
how can I acheive this?
Thanks in advance
Maybe through the use of a 'virtual transaction' (I just made that phrase up, so please don't search for it!)
Imagine that you have two tables, A and B, in the destination database, which have identical structures.
Currently, table A is 'Active' and table B is not being referenced.
Your process works with table B – doing the truncate and load.
The final step of the process – assuming everything worked as intended – is to 'swap' tables A and B.
You might implement this 'swap' via sp_rename, or through an ALTER VIEW or through use of a synonym – you have some options there.
As long as your final 'swap' process is atomic, all should be well.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply