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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply