query processor ran out of internal resources - extremely long sql query
The error message is there for a reason. Using UNION on thousands of tables in a single query is performance suicide. Yes, I understand that your reporting application is already in production, but I would still strongly recommend that you to go back to your development team and update the application or, even better, rebuild its logic in a stored procedure instead.

As you suggest in your question, create a temp table, run each of those queries sequentially and dump the results into the temp table. Then, compile the report from the temp table. All of this can be done in a stored procedure, which will also cover other important aspects like security and performance.

This carries a range of advantages:


Temp table can be indexed for performance
You won't bloat your server's query plan cache (and in the process evict pretty much any other query plan that has ever been cached)
The query won't take forever to "compile" (i.e. turn into an execution plan)


I'm not trying to mess with you. It's my firm conviction that if you don't rebuild the report now, you will most likely have to some day, going forward.