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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply