Select into - restrictions

• You cannot specify a table variable or table-valued parameter as the new table.
• You cannot use SELECT…INTO to create a partitioned table, even when the source table is partitioned. SELECT...INTO does not use the partition scheme of the source table; instead, the new table is created in the default filegroup. To insert rows into a partitioned table, you must first create the partitioned table and then use the INSERT INTO...SELECT FROM statement.
• Indexes, constraints, and triggers defined in the source table are not transferred to the new table, nor can they be specified in the SELECT...INTO statement. If these objects are required, you can create them after executing the SELECT...INTO statement.
• Specifying an ORDER BY clause does not guarantee the rows are inserted in the specified order.
• When a sparse column is included in the select list, the sparse column property does not transfer to the column in the new table. If this property is required in the new table, alter the column definition after executing the SELECT...INTO statement to include this property.
• When a computed column is included in the select list, the corresponding column in the new table is not a computed column. The values in the new column are the values that were computed at the time SELECT...INTO was executed. 

When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:

• The SELECT statement contains a join, GROUP BY clause, or aggregate function.
  
• Multiple SELECT statements are joined by using UNION.
  
• The identity column is listed more than one time in the select list.
  
• The identity column is part of an expression.
  
• The identity column is from a remote data source.