INSERT INTO SELECT vs SELECT INTO

December 3, 2014 by Kenneth Fisher

INSERT INTO SELECT and SELECT INTO may be very similar commands but they have some important differences. Every now and again I see people getting confused by the similarities and missing the differences. So here is a quick check list.

Similarities

They look similar (I think this one throws people a lot.)
They are both part of larger commands.
They both insert rows into a table.
Both can be minimally logged under the right circumstances.

Differences

INSERT INTO SELECT inserts into an existing table.
SELECT INTO creates a new table and puts the data in it.
- All of the columns in the query must be named so each of the columns in the table will have a name. This is the most common mistake I see for this command.
- The data type and nullability come from the source query.
- If one of the source columns is an identity column and meets certain conditions (no JOINs in the query for example) then the column in the new table will also be an identity.

Basic syntax

INSERT INTO SELECT

CREATE TABLE MyTable (name varchar(255));
GO
INSERT INTO MyTable
SELECT name
FROM sys.databases;
GO

SELECT INTO

SELECT name INTO MyTable
FROM sys.databases;
GO

Category: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL | Tags: code language, language sql, microsoft sql server, sql statements, T-SQL

5 thoughts on “INSERT INTO SELECT vs SELECT INTO”

Lee Everest says:

December 5, 2014 at 1:56 PM

I see a lot of people that don’t know you can make INSERT SELECT as fast as SELECT INTO by using with (tablock) or (tablockx). Blog on those to continue!

Lee

Kenneth Fisher says:

December 5, 2014 at 1:59 PM

Actually someone suggested that I do a post on the requirements for minimal logging for each. I’ll probably do it early next year.

Reply
- David VM says:
  
  January 10, 2019 at 9:16 AM
  
  I am curious to know about about minimally logging SELECT INTO and INSERT INTO. Can you point me to what you found?
  
  Reply
  - Kenneth Fisher says:
    
    January 10, 2019 at 10:49 AM
    
    Unfortunately I’m not all that great at getting things to minimally log. That said here is a post by MS on the subject and make sure you read Sunil’s post from 2008 (the link is in the post below)
    
    https://blogs.msdn.microsoft.com/sql_server_team/sql-server-2016-minimal-logging-and-impact-of-the-batchsize-in-bulk-load-operations/
  - Robert Carnegie says:
    
    July 13, 2020 at 6:06 AM
    
    KF, thank you for the advice on this page. I see your latest comment refers to SQL Server 2016 though presumably your article written in 2014 does not 😉 I’m on SQL Server 2014 so I don’t know if I can achieve minimal logging on INSERT – SELECT FROM.
    Oh well… I’d just rewritten a script which was doing,
    SELECT IDENTITY(…) AS row_id, * INTO table FROM ( SELECT * FROM table1 UNION ALL SELECT * FROM table2 UNION ALL SELECT * FROM table3 ) [Roberts_sub_query]
    So I can just un-rewrite it. I wanted to SELECT separately from each table, but I don’t have to. I wasn’t considering minimally logged performance but my colleagues have made remarks about it… and these are big tables.