INSERT INTO SELECT vs SELECT INTO

5

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

5 thoughts on “INSERT INTO SELECT vs SELECT INTO

  1. Lee Everest says:

    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

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

      • David VM says:

        I am curious to know about about minimally logging SELECT INTO and INSERT INTO. Can you point me to what you found?

        • 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:

          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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,148 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: