February 11, 2011 at 11:35 am
Greetings everyone 😀
I have a Merge statement I am using as part of an ETL process. It works as intended except for one small piece.
Basically here is the process...this is on an Orders table
Delete records in the target table that are canceled or changed (for archival purposes on certain changes)
Update records in target table that have updated records in the source
And last but not least, insert new records.
This works fine however I have an output deleted.* into an archive table at the end.
The problem is I realize, that deleted.* also includes the old records from the update change as well.
So my question is...is there a way to out put ONLY the records that were affected by the delete statement?
I know I can just create the delete process as its own query which executes effectively and then issue the merge statement but my intention in using the merge was doing all this in one step 🙂
Link to my blog http://notyelf.com/
February 11, 2011 at 12:20 pm
The best option I can think of is output to a temp table, including the $action value (Output clause option). Then insert from the temp table into the archive table where the Action column (target of the $action value) = 'DELETE'.
merge ...
output $action, deleted.* into #T (Action, ...);
insert into ArchiveTable(...)
select ...
from #T
where Action = 'DELETE';
Makes it two steps, but I think it's going to be the most efficient way to do this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply