Which is Faster – INSERT INTO SELECT or SELECT INTO? – Interview Question of the Week #187

Question: Which is faster, INSERT INTO ... SELECT or SELECT ... INTO?

Answer: In my original large-load test, SELECT INTO was faster. It is a useful candidate for creating a new staging table, but the syntax alone does not guarantee the winner for every workload.

The question came from a Comprehensive Database Performance Health Check. A customer needed to move a large amount of data between tables and the insert was taking too long. We investigated the actual load rather than treating the existing statement as the only way to do it.

The original experiment built ten million rows from WideWorldImporters‘ Warehouse.StockItemTransactions, then compared an insert into a pre-created table with creation and loading through SELECT INTO. These are the measurements reported in that experiment:

The third run used more total CPU while finishing sooner. That is a good reason to look at both CPU and elapsed time. These historical measurements are not predictions for your storage, current SQL Server build or table definition.

Repeat the comparison in a lab

Open a fresh query session for this lab so its temporary-table names cannot collide with your other work. This version keeps the same WideWorldImporters columns and cross-join source, but uses local temporary targets so it does not leave permanent experiment tables behind. It starts with one million rows to keep the first run manageable. It requires SQL Server 2016 or later, room in tempdb and the sample database. The full source remains in the article because a screenshot of its first screenful would not help.

USE WideWorldImporters;
GO
SET NOCOUNT ON;
DROP TABLE IF EXISTS #LoadSource, #InsertIntoTest, #SelectIntoTest;

DECLARE @Rows int = 1000000; -- use 10000000 only in a suitable lab
SELECT TOP (@Rows)
       SI.StockItemTransactionID, SI.StockItemID, SI.TransactionTypeID, SI.CustomerID, SI.InvoiceID, SI.SupplierID, SI.PurchaseOrderID, SI.TransactionOccurredWhen, SI.Quantity, SI.LastEditedBy, SI.LastEditedWhen
INTO #LoadSource
FROM Warehouse.StockItemTransactions AS SI
CROSS JOIN Warehouse.StockItemTransactions AS SI1;

-- Create the INSERT target before its timed load.
SELECT TOP (0) StockItemTransactionID, StockItemID, TransactionTypeID,
       CustomerID, InvoiceID, SupplierID, PurchaseOrderID,
       TransactionOccurredWhen, Quantity, LastEditedBy, LastEditedWhen
INTO #InsertIntoTest FROM #LoadSource;

SET STATISTICS TIME ON;
INSERT INTO #InsertIntoTest (StockItemTransactionID, StockItemID, TransactionTypeID,
       CustomerID, InvoiceID, SupplierID, PurchaseOrderID,
       TransactionOccurredWhen, Quantity, LastEditedBy, LastEditedWhen)
SELECT StockItemTransactionID, StockItemID, TransactionTypeID,
       CustomerID, InvoiceID, SupplierID, PurchaseOrderID,
       TransactionOccurredWhen, Quantity, LastEditedBy, LastEditedWhen
FROM #LoadSource OPTION (MAXDOP 1);
SET STATISTICS TIME OFF;

SET STATISTICS TIME ON;
SELECT StockItemTransactionID, StockItemID, TransactionTypeID,
       CustomerID, InvoiceID, SupplierID, PurchaseOrderID,
       TransactionOccurredWhen, Quantity, LastEditedBy, LastEditedWhen
INTO #SelectIntoTest
FROM #LoadSource OPTION (MAXDOP 1);
SET STATISTICS TIME OFF;

SELECT 'Source' AS Test, COUNT_BIG(*) AS RowsCopied FROM #LoadSource
UNION ALL SELECT 'INSERT SELECT', COUNT_BIG(*) FROM #InsertIntoTest
UNION ALL SELECT 'SELECT INTO', COUNT_BIG(*) FROM #SelectIntoTest;
DROP TABLE #InsertIntoTest, #SelectIntoTest, #LoadSource;

A current one-million-row run

On SQL Server 2025 CU9 with WideWorldImporters, the script above copied 1,000,000 rows into each target and returned the same source count. The following Messages capture is one run, with INSERT SELECT first and SELECT INTO second. In this smaller serial test, the two methods were close and INSERT SELECT finished slightly sooner. It does not overturn the original customer’s ten-million-row result or establish a universal winner.

One-million-row serial loads. Captured in SSMS on SQL Server 2025.

Both timed loads use MAXDOP 1. The INSERT target is created before timing; SELECT INTO includes creation of its new target. Repeat in both orders and compare row counts, execution plans, logging and elapsed time. A warm source cache, concurrent work and target indexes can materially change the result.

SELECT INTO creates a new table. It does not transfer source indexes, constraints or triggers for you. INSERT SELECT works with a table you have already defined, including its required structure and rules. Logging depends on the recovery model and operation; INSERT SELECT can also qualify for minimal logging and parallel insertion into an eligible heap with TABLOCK. Do not change a production recovery model merely to improve this benchmark.

My useful finding was that changing the load method helped this customer. The conclusion should be to test an appropriate bulk-loading method for the actual target, rather than to promise that SELECT INTO always wins.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.