New version of SELECT INTO that supports indexes - by Jamie Thomson

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


10
3
Sign in
to vote
ID 490142 Comments
Status Closed Workarounds
Type Suggestion Repros 1
Opened 9/16/2009 6:22:45 AM
Access Restriction Public

Description

SELECT ... INTO is a very useful feature of T-SQL and is used heavily in data warehousing scenarios.
I use it A LOT for ETL/data warehousing (i.e. I copy data from my warehouse into a "work area" so that I can transform my incoming new data and compare against what I already have in my work area). However, each time I copy a table across I have to manually create all of the indexes that may be necassary in order to do transformation and that is both time consuming and requires extra development.

Extra: I forgot to mention when I first submitted this that SQL Azure does not support SELECT ... INTO for the very reason that it does not support indexes. Hence it is even more vital that we get a new construct that enables us to do this.

[Don't miss additional comments from other people below]
Sign in to post a comment.
Posted by navogel on 4/11/2011 at 5:09 AM
SQL Server PDW already supports a SELECT into a table with index creation using the CREATE TABLE AS (CTAS) syntax. Supposedly Microsoft intend to align the PDW version of SQL with the other product editions in the future, so it would make sense to add CTAS to all editions.
Posted by Jan [MSFT] on 3/24/2011 at 4:36 PM
Hello Jamie,

Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”. If you feel that this is worth reconsidering, feel free to send a message and we will take another look.

Jan
SQL Server Engine
Posted by Jamie Thomson on 9/27/2009 at 3:23 PM
Thanks Isaac! I'll keep badgering :P)

Regarding the workaround (i.e. do it manually), yes, that is indeed a workaround. But please don't let the fact that a workaround exists prevent you from doing this. SELECT ... INTO is a very very useful construct, but don't forget that it is not supported in SQL Azure!
Posted by Microsoft on 9/24/2009 at 3:46 PM
"Please don't just give a canned response."

Connect Bot activated.

Really, though, this sounds like a reasonable ask. I can't make promises, but we'll definitely consider it. This one does seem to have a work around: do it manually like you do today. :-/

Cheers,
-Isaac
Posted by navogel on 9/16/2009 at 7:15 AM
A more complete solution would be to deprecate SELECT INTO and introduce the SQL Standard CREATE TABLE AS syntax used by other DBMSs, with full support for definition language. That could then address other limitations of SELECT INTO such as that you can't explicitly control nullability and you can't specify a non-default filegroup.

SQL Server is the only DBMS that has this odd proprietary SELECT INTO syntax. SELECT INTO is supposed to be for variable assignment.