INSERT INTO SELECT vs SELECT INTO

2

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

2 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

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

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

Join 1,653 other followers

Follow me on Twitter

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