INSERT INTO vs SELECT INTO
What is faster to use…
INSERT INTO or SELECT INTO?
I have read many blog posts comparing INSERT INTO to SELECT INTO, yet so many still get it wrong. In this post, I will provide the full picture of performance differences between INSERT INTO and SELECT INTO and how to use it correctly.
Let’s Get Dirty
As this comparison takes place across four versions of SQL Server, namely 2012, 2014, 2016 & 2017. I will keep this post short. Therefore, if you are interested in the details of the scripts used & more in these tests, see this post: Get Dirtier Here.
Each test was conducted 3 times where the average was used for the conclusion of these tests.
- INSERT INTO without TABLOCK
- INSERT INTO with TABLOCK
- INSERT INTO with OPTION (MAXDOP 1)
- SELECT INTO with OPTION (MAXDOP 1)
- SELECT INTO without OPTION (MAXDOP 1)
The below table shows a comparison of execution time (ms) between INSERT INTO & SELECT INTO across the versions of SQL Server. Orange depicts single threaded executions and green depicts parallelly treaded executions.
What did we learn?
- SQL Server 2012 does not support parallel “Table Insert” executions;
- SQL Server 2014 SELECT INTO is faster than INSERT INTO by default as SELECT INTO is the only “Table Insert” operator that can run parallelly by default;
- SQL Server 2016 introduced parallelism for the INSERT INTO “Table Insert” operator, making it just as fast as SELECT INTO;
- SQL Server 2016/2017 INSERT INTO will only run parallelly if you use the TABLOCK hint;
In the end, we can conclude that SELECT INTO and INSERT INTO have equal performance, except in SQL Server 2014.