You cited two different articles that discuss two different things. The first article compares INSERT SELECT with SELECT INTO for temporary tables and later compares these two in general. In general INSERT SELECT is slower because it's a fully logged operation. SELECT INTO is minimally logged in the simple and bulk-logged recovery models. The last comment you cited is about inserting into with(tablock), this with(tablock) can make insert into minimally logged under some additional conditions: it should be a heap and have no indexes. You can find the complete guide here: The Data Loading Performance Guide It can be summarized in this table: Note the updates for SQL Server 2016 and later in SQL Server 2016, Minimal logging and Impact of the Batchsize in bulk load operations by Parik Savjani (a Senior Program Manager with the Microsoft SQL Server Tiger team). The updated table is: Regarding the first article. It discusses the particular case for temporary tables.
The tempdb database is special because it's always in the simple recovery model, and because logging in tempdb is different. On every server restart, tempdb is recreated, this means no crash recovery is made for tempdb, and this means that logging in tempdb does not need any "after" image of modified data, only the "before" image to be able to do a rollback if there is a need. This leads to the fact that insert into..select is also minimally logged in tempdb even without tablock hint (in case of a heap that was discussed in the first article).
Conclusion "select into vs insert..select under logging aspect":
In case of a heap, insert into..select performs similarly to select into in case of temporary tables, and in general is slower when tablock hint is not used. The second aspect is the possibility of parallel execution. Select into can be executed in parallel starting with SQL Server 2014, and parallel insert...select was first implemented in SQL Server 2016. I did not reproduce any performance difference between select into and insert into ..select for temporary tables on SQL Server 2012, all executed in serial.
The SQL Server SELECT INTO statement
The “SELECT INTO” statement copies data from one table into a new table.
SYNTAX:
Copy all columns into a new table:
SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;
Copy only some columns into a new table:
SELECT column1, column2, column3, ...
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;
A new table will be created with the column names and types as defined in the old table. You can create new column names using the “AS” clause.
