Hi,
>> I've marked both response as correct
That is great, this is the right way in my opinion if a question get several right responses. Unlike a chat, a forum thread is here to stay, and future users (especially those that come from searching engine) might see the responses and choose a different
solution. Therefore all currect answer should be marked.
1. With that was said, I think that your solution is
not a one that I recommends for most cases. There is no reason to do any looping in this case. SQL Server is a Tabular database, which based on SET, and we should avoid looping through records and working with it record by record, as much as we can!
2. You do not have to use the temporary table in order to avoid looping. In both links that posted the job is done in two steps: firstly we get the information, and secondly we use the data from step 1. You can avoid using temporary table and work in one
step, but using the approach in the second link that I posted: create one dynamic query using "for xml" hint, and execute it directly.
>> Off the top of my head, would I be correct that Idr_p's approach would work in memory
basically SQL Server always work in memory. Even when we read data (select) or write it (insert/update) to table, which is on disk, SQL Server will firstly read the data to the memory and work in memory. Once in a while, SQL Server do a CHECKPOINT operation
which writes the current in-memory modified pages from memory to disk.
The big disadvantage (which is one of the advantage as well, depending on the case) of using temporary table is that the code execute in two steps, therefore the SQL Server Query engine built an execution plan for each step separately which mean that this
plan is optimized for the specific step and not for the entire process that we want to do. When you use inline code the SQL Server query engine can parse the entire text and build a combine execution plan which is optinize for the whole process. In order to
chose what fit your need you need to examine it case by case.
** For more information regarding "Temp table", "Table-Valued Parameters", and "Table Variables" check this link:
https://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/
** I recommend to search Google for: SQL Server temp table vs inlin