In SQL Server we have the ability to use temp tables to perform data transformation kind of activities within a stored procedure. As SQL Server evolved table variables were introduced, it is a hotly debated topic when it comes to temp table and table variables which is better. Recently there was a discussion regarding the same, the views were from either camps were solid. Here is a link to the discussion, i did not want to just recreate it here since the folks involved in this topic are very seasoned professionals.
Another Useful link:
Based on reading the discussions and the links, it is best to first understand the requirements of the system and then choose a strategy rather than jump to conclusions.