Creation of table by SELECT INTO statement

SELECT … INTO is being used to create a table.

It is usually easier to understand tables created with a CREATE TABLE statement.  However, SELECT INTO can be very useful for creating temporary tables.

Available in

 SQL Monitor

SQL Prompt

 SQL Code Guard

SELECT…INTO became popular because it was a faster way of inserting data than using INSERT INTO…SELECT…. This was mainly due to the SELECT…INTO operation being, where possible, bulk-logged. Although INSERT INTO can now be bulk-logged, you may still see this performance advantage in SQL Server 2012 and 2014, because SELECT…INTO can be parallelized on these versions, whereas support for parallelization of INSERT INTO only emerged in SQL Server 2016. However, with SELECT…INTO, you still have the task of defining all required indexes and constraints, and so on, on the new table.

Generally, SELECT…INTO is still a useful shortcut for development work, but it no longer has a clear performance advantage, and is of doubtful value for a live database. It is not part of the SQL Standard. It is usually easier to work with tables created with a CREATE TABLE statement because you then have the advantage of specifying constraints and datatypes in advance, and this also means that you're less likely to allow inconsistencies to sneak into the data.

See also: When to use the SELECT…INTO statement