Daniel Janik is not a fan of SELECT INTO:
This query for AdventureWorks will dump all of its results into a table named #MyDuplicateCities. Note that there is no CREATE TABLE statement. The INTO [tablename] will create the table for you.
Running this query a second time will result in failure if you haven’t dropped the #MyDuplicateCities table.
Using this syntax can be really helpful if you just need to do some quick and dirty cleanup; however, it should be avoided for stored procedures. Here’s why…
There are some trade-offs here and good arguments either way. The comments tend to take the pro approach, so they’re worth reading as well.