Depends on what your partitions are based on. Mine are based on a "CampaignID"... any campaign that hasn't had a new row added to it in 90 days gets archived. I just do a select/group by on the CampaignID and the Max LoadDate vs GETDATE() and loop through the campaigns to do the Insert/Delete's. It's actually a double loop... inside loop does the archival insert/delete 20K rows at a time as previously determined by divided the number of rows present for that campaign by 20k.

I can't use a partitioned view to do it because I need to log which underlying table the campaign was in for possible restore processes. As you suggest, a bit of dynamic SQL takes care of all that.