That's a TON of temp tables, and not one of them has any primary key or any indexing.   At least get a useful primary key (use field or fields that...

Thanks Jeff for reminding me of the rest of the requirements on quirky update.   It's been 9 years since I had the chance to actually use it, and I'd long...

This is known in the business world as BOM, or Bill Of Materials.   That's another avenue you can investigate online, in addition to the excellent suggestions Luis has made.

The calendar table implementation would have to be somewhat different.   Just adding a table and thinking that such would solve the problem was just not realistic.  You would have to...

The problem may be with ROBOCOPY.   It was never designed to be run from an Agent Job, so it doesn't pay any attention to whether or not a console is...

Have you seen any errors in any of the SQL Server logs?   Also, are you using a BCP format file?  If you have a row that won't fit the format,...

Without your code for the stored procedure, it's going to be hard to see what's going on.   However, if you use temp tables, you probably ought to give them PRIMARY...

I'm not entirely sure there's a set-based way to accomplish the UPDATE portion of your task, as there's always going to be a need to know exactly how much of...

Another alternative involves "quirky update".   It does require, however, that the update occur in PRIMARY KEY order, so I added a clustered index to the table:
create table #tempData (

Or here:
https://community.spiceworks.com/topic/358653-robocopy-incremental-copy-mir-or-xo-what-s-your-choice

Also take a look here:  https://answers.microsoft.com/en-us/windows/forum/windows_7-files/is-using-robocopy-with-the-mir-switch-safe/ad91976f-ebb5-4ba8-88a0-532807f497f0?auth=1

I don't quite know what the /MIR option does for ROBOCOPY operations, but when you say it sometimes mirrors, but sometimes not, we need to know what that actually means....

Have you ever used UNION or UNION ALL ?    Try the following, which isn't all that bad in terms of how you'll have to expand it as the number of...