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

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, language sql, microsoft sql server, sql statements, T-SQL