The Rivalry Between SELECT INTO and INSERT INTO

Data can be inserted into one temp table from another a couple of ways. There is the INSERT INTO option and the SELECT INTO option.

Are you devoted to one option over the other? Maybe you’re used to one and never experimented with the other. Let’s test each and compare performance to find out which is more efficient.

Introducing…INSERT INTO

We’ll start by creating a temp table for inserting our test data. For this example, we’ll use the StackOverflow2013 database and focus on the Users table. Our temp table is going to be a copy that we can use to insert the Users table data:

CREATE TABLE #TempUsersInsert(
	[Id] [int] NOT NULL,
	[AboutMe] [nvarchar](max) NULL,
	[Age] [int] NULL,
	[CreationDate] [datetime] NOT NULL,
	[DisplayName] [nvarchar](40) NOT NULL,
	[DownVotes] [int] NOT NULL,
	[EmailHash] [nvarchar](40) NULL,
	[LastAccessDate] [datetime] NOT NULL,
	[Location] [nvarchar](100) NULL,
	[Reputation] [int] NOT NULL,
	[UpVotes] [int] NOT NULL,
	[Views] [int] NOT NULL,
	[WebsiteUrl] [nvarchar](200) NULL,
	[AccountId] [int] NULL
);

We’ll run the following so we can track stats on our insert:

SET STATISTICS IO, TIME ON;

We’re ready to use INSERT INTO and select every record from the Users table:

INSERT INTO #TempUsersInsert
SELECT [Id]
	,[AboutMe]
	,[Age]
	,[CreationDate]
	,[DisplayName]
	,[DownVotes]
	,[EmailHash]
	,[LastAccessDate]
	,[Location]
	,[Reputation]
	,[UpVotes]
	,[Views]
	,[WebsiteUrl]
	,[AccountId]
FROM Users;

Plugging our stats into StatisticsParser shows us we had 30,050 logical reads which took almost 9 seconds to complete:

The Challenger…SELECT INTO

Next up is inserting data using SELECT INTO. No need to explicitly create a temp table. We’ll use SELECT INTO and select all records from the Users table:

SELECT *
INTO #TempUsersInto
FROM Users;

We had more logical reads but this time we only needed about 3 seconds for our inserts to complete.

What’s the Difference Here?

If we run both queries together and view the execution plans, something sticks out:

Did you catch it? Notice that the SELECT INTO went parallel. This enhancement became possible with the release of SQL Server 2014. Before that, SELECT INTO was still serial.

Starting with SQL Server 2016, it is possible to get parallelism with INSERT INTO under the right circumstances…though it requires an extra step. That extra step is using the WITH(TABLOCK) hint. If we start fresh, add the WITH(TABLOCK) hint to our example, and run our inserts together, we’ll see parallelism on both.

Fight Forever

It was close but I’m going to give this round to SELECT INTO over INSERT INTO. From my experience, SELECT INTO is usually going to give you the better performance.

Thanks for reading!