SELECT ... INTO is a very useful feature of T-SQL and is used heavily in data warehousing scenarios.
I use it A LOT for ETL/data warehousing (i.e. I copy data from my warehouse into a "work area" so that I can transform my incoming new data and compare against what I already have in my work area). However, each time I copy a table across I have to manually create all of the indexes that may be necassary in order to do transformation and that is both time consuming and requires extra development.
Extra: I forgot to mention when I first submitted this that SQL Azure does not support SELECT ... INTO for the very reason that it does not support indexes. Hence it is even more vital that we get a new construct that enables us to do this.
[Don't miss additional comments from other people below]