i agree with the comment above. merge statement is fabulous. you got your data instaging with a bulk load insert. then you can handle updates, deletes and inserts the way you want between staging and destination tables.

before use audit columns and tables to handle what to extract (i use cozyroc to handle dynamic meta data) and in 2k8, cdc was introduced.