Yet another reason to upgrade to 2008; MERGE. I wrote this for a BI project I was working on recently.. This SP is self explanatory. It Uses dynamic SQL to build a MERGE statement over a linked server with some additional parameters; i.e. Oracle vs. SQL, limit the source data, etc.
I'm using this in combination with SSIS as part of an ETL task. i.e. use a data flow task to interate through the sys.tables in a given schema and put the table names into an ADO recordset object, then execute this SP using an expression inside a for each loop container.
Enjoy! (just give me some credit if you use it :)-)