Used to be partially true in SQL 7.5 and before. Schema locks on tempdb were needed in order to create temp tables back then. In any version of SQL Server from SQL 2000 or later, that is no longer true. But even in the older versions, the locks only lasted as long as it took to create the table, not for the duration of the inserts, if I remember correctly.

On the main subject, I use Select Into when the structure of the temp table is subject to change without notice, or when I'm trying to debug truncation issues and so on. I generally use Create...Insert...Select for temp tables where I want to control the structure of the temp table. In either case, I usually add a conditional Drop Table before the create step.

IF OBJECT_ID(N'tempdb..#T') IS NOT NULL

DROP TABLE #T;

CREATE TABLE #T

(ID INT IDENTITY

PRIMARY KEY,

MyColumn1 INT NOT NULL);

INSERT INTO #T

(MyColumn1)

VALUES (1),

(2),

(5);

Like that.

Makes it so the script can be run repeatedly for debugging/testing/refactoring.

Breaking it up that way makes it more "self-documenting", it my opinion. I find it much easier to overlook an "INTO" in a Select statement, than to overlook a "CREATE TABLE" with some whitespace around it.

Plus habits from SQL 2000 and before, where mixing DDL and DML could cause performance issues - not as important any more, but it's a habit and I haven't bothered to change it.

So I tend towards explicitly creating temp tables.

One very useful application of Select Into, though, is when you get a truncation error from an ETL process that was working before. Use Select Into, pull the structure of the resulting table, compare it to your target table, and the column with a longer max_length will usually leap right out. If you name the columns the same as the target table, you can even do an Outer Join on the two sets of column definitions and find where the temp table has a bigger scope. Speeds up debugging that kind of thing marvelously when you have a dozen or more varchar columns, and all you get from the SQL engine is "string or binary data would be truncated", with no indication of what column/row you need to look at.

There are lots of other little shortcuts like that where Select Into can save a lot of time and work.