First of all the main difference between this 2 statements is that SELECT INTO will create a NEW table, while INSERT INTO requires a table to exist.
Most of the times when we just want to create a temp table on the fly, we use SELECT INTO, just because it is so easy and simple to use.
SELECT *, 0 as NewColumn1, 0 as NewColumn2 INTO #temp FROM Person.CountryRegion
But not all the times is the best solution! Specially when we have many records to insert in the table, causing the query execution to take several minutes.
SELECT INTO statement won’t show up in the execution plan, therefore it won’t be possible to track and improve the performance of your query or stored procedure, that’s why it is recommended to ALWAYS create the temp table first, and then use INSERT INTO to insert the records:
CREATE TABLE #temp ( CountryRegionCode varchar(2) NOT NULL, Name varchar(50), ModifiedDate datetime, NewColumn1 int, NewColumn2 int ) INSERT INTO #temp SELECT *, 0 as NewColumn1, 0 as NewColumn2 from Person.CountryRegion