It's also possible to create a temp table with the syntax:

Select (fields) into #temp from (table).

This is much easier than defining the table, but I've heard though that doing this sometimes raises performance issues. I'm guessing that the issues relate to things already mentioned in this discussion like the size of the created table (a large recordset would be more likely to cause spill-over into virtual/disc memory), etc.

Anyone care to comment?

One way or the other it can be a handy debugging tool if you need to find out what's happening in the middle of a complicated procedure and you don't want to re-define temp tables every time you make a small change. At any point in the procedure, you just use:

Select * from #temp

return

It can result in much quicker debugging than when using derived tables...