Before we directly jump to the topic, I would like to give a basic introduction of SELECT..INTO statement.

As, we know that, select into statement can be used to create new table with same table structure and to insert a base table rows in new table. We need to consider following things before using this.

1.  Column constraints are not gets created in new table
2.  If a selected column is computed then corresponding column in new table will not be computed column. The values in new columns are the values that were computed at the time SELECT…INTO was executed.
3.  SELECT…INTO statement does not cause blocking for duration of select statement.
4.  Generally, SELECT…INTO is measurably faster than separate create and insert statements. I will try to demonstrate the same in this post.

Following steps are performed. I have run this test on SQL 2005 sp3.

Step-1 Create Base table and populate default data.

IF object_id('IntoTestBaseTable')  IS NOT NULL
DROP TABLE IntoTestBaseTable;
GO
CREATE TABLE IntoTestBaseTable ( c1 INT , c2 NVARCHAR (MAX) );
GO
DECLARE @i AS INT;
SELECT @i = 2500000;
WHILE (@i > 0)
BEGIN
      INSERT INTO IntoTestBaseTable
      SELECT @i, CAST (@i AS NVARCHAR (MAX)); 
      SELECT @i = @i - 1;
END
GO

Step-2 Clear cache and buffers

NOTE:- Do not run this on live/production environments.

DBCC FreeProcCache
DBCC DropCleanBuffers
GO

 Step-3 Measure the time required for SELECT..INTO

SET STATISTICS TIME ON;
GO
IF object_id('tempdb..#Tmp1IntoTestBaseTable') IS NOT NULL
    DROP TABLE #Tmp1IntoTestBaseTable;
GO
SELECT *
INTO   #Tmp1IntoTestBaseTable
FROM   IntoTestBaseTable;
 
--In my case, I get following details
--SQL Server Execution Times:
--CPU time = 4204 ms,  elapsed time = 4526 ms.

 Step-4 Create a second blank temp table. I have created blank table using SELECT..INTO statement. We can also use CREATE TABLE statement for this.

IF object_id('tempdb..#temp2IntoTestBaseTable') IS NOT NULL
   DROP TABLE #temp2IntoTestBaseTable;
GO
SELECT *
INTO   #temp2IntoTestBaseTable
FROM   IntoTestBaseTable
WHERE  0 = 1

Step-5 Clear cache and buffers
NOTE:- Do not run this on live/production environments.

DBCC FreeProcCache
DBCC DropCleanBuffers
GO

Step-6 Measure the time required for INSERT INTO

INSERT INTO #temp2IntoTestBaseTable
SELECT *
FROM   IntoTestBaseTable;
--In my case, I get following details
--SQL Server Execution Times:
--   CPU time = 15125 ms,  elapsed time = 52301 ms.

Step-7 DROP temp tables

 IF object_id('tempdb..#temp2IntoTestBaseTable') IS NOT NULL
    DROP TABLE #temp2IntoTestBaseTable;
GO
 
IF object_id('tempdb..#Tmp1IntoTestBaseTable') IS NOT NULL
    DROP TABLE #Tmp1IntoTestBaseTable;
GO

Now, we can clearly see the execution time difference between SELECT INTO and CREATE & INSERT. The reason for this is the log records get created in step is different. SELECT..INTO performs as a bulk insert and its minimally logged operator.  As shown above we are inserting records into TEMPDB and recovery mode of TEMPDB is always SIMPLE, this becomes minimally logged operation.

Even if we do not use temporary table and use the physical table in the same database, SELECT..INTO is still faster than separate CREATE & INSERT. This is due to the log records are generated for each statement. Following steps demonstrates the same.

NOTE:-I have changed the recovery model of database to Balk-logged for this demo.

Step-1 Clear cache and buffers
NOTE:- Do not run this on live/production environments.

DBCC FreeProcCache
DBCC DropCleanBuffers
GO

Step-2 Measure the time required for SELECT..INTO

 IF object_id('Tmp1IntoTestBaseTable') IS NOT NULL
    DROP TABLE Tmp1IntoTestBaseTable;
GO
SELECT *
INTO   Tmp1IntoTestBaseTable
FROM   IntoTestBaseTable;
--SQL Server Execution Times:
--CPU time = 4328 ms, elapsed time = 12770 ms.

Step-3 Look at the number of log records generated for test

I have taken this script form following post:

http://sqlblog.com/blogs/tibor_karaszi/archive/2010/03/22/are-log-records-removed-from-ldf-file-for-rollbacks.aspx

SELECT count(*) FROM fn_dblog(NULL, NULL) 
WHERE [Transaction ID] = (SELECT TOP(1) [Transaction ID] 
                          FROM fn_dblog(NULL, NULL)
                          WHERE AllocUnitName LIKE '%Tmp1IntoTestBaseTable%'
                          ORDER BY [Current LSN] DESC)

--13496 log records generated

Step-4 Clear cache and buffers
NOTE:- Do not run this on live/production environments.

DBCC FreeProcCache
DBCC DropCleanBuffers
GO

Step-5 Create a second blank physical table. I have created blank table using SELECT..INTO statement. We can also use CREATE TABLE statement for this.

IF object_id('temp2IntoTestBaseTable') IS NOT NULL
    DROP TABLE temp2IntoTestBaseTable;
GO
SELECT *
INTO   temp2IntoTestBaseTable
FROM   IntoTestBaseTable
WHERE  1 = 0

Step-6 Measure the time required for INSERT INTO

INSERT INTO temp2IntoTestBaseTable
SELECT *
FROM   IntoTestBaseTable
--SQL Server Execution Times:
--CPU time = 15187 ms,  elapsed time = 27503 ms.

Step-7 Look at the number of log records generated for test

SELECT count(*) FROM fn_dblog(NULL, NULL) 

As we can clearly see that INSERT generates lots more log records then SELECT..INTO statemnet.

 So, After looking at these two examples we can conclude that SELECT.. INTO is faster than CREATE & INSERT.