What are the idiomatic approaches to cross-server upserts?

This is a very common problem, faced by virtually every DBA who has to responsibilities to both application and BI teams. Consider the following: 1. You have two T-SQL **servers**, Production and Reporting. 2. Every day, at 6am, you want to copy new data from Production to Reporting. There may be filters or some other ETL done in this process. 3. You don't want to copy any more data than you need to, so ideally you want a MERGE of some sort. **What are the idiomatic solutions to this problem?** In my own research, I have found: - Linked Servers - They are very slow when doing upserts and are prone to sending your whole table over the network when it only needed a few rows. - SSIS - Painfully unportable, very old, and forces you to jump through extra hoops like putting staging tables on both servers. I hope that there is something better.