Merge Into with multiple execution 1

[TheLazyPig]

Hi!

I would like to know if I can use merge with multiple executions when matched.

When matched, it should insert the matched record into a history table first and then update the main table.
When not matched, it will insert into the main table.

I just wanted to back up the matched record for history.



Thanks!

 

[carp]

I'm not aware of any way to do this (a more clever person will soon post a fine solution, I'm sure). In the meantime, I think you'll need to take the more traditional approach of either using two statements (merge the main table, insert the history table) or put a trigger on the main table that maintains the history table. Hoping the more clever person comes along to enlighten us both.