I recently developed a stored procedure which essentially queries data from several different tables, manipulates it, and then inserts the result in a certain table.
It ended up being one INSERT statement with many subqueries and was around 300 lines. The code was very performant; however, it is not very readable and may have other drawbacks.
Is it within recommended usage to write a query like so:
SELECT ... INTO #tempfoo FROM foo; SELECT ... INTO #tempbar FROM bar; SELECT ... FROM #tempfoo INNER JOIN #tempbar ON #tempfoo.id = #tempbar.id;
Or is it better to write it like this, even though it can become somewhat unreadable:
SELECT ... FROM ( SELECT ... FROM foo ) AS foo INNER JOIN ( SELECT ... FROM bar ) AS bar ON foo.id = bar.id;
Note: I know this is a simple example, but imagine if you have layers of nested subqueries and four or more table joins.