Home Dashboard Directory Help
Search

New version of SELECT INTO that supports indexes by Jamie Thomson


Status: 

Closed
 as Won't Fix Help for as Won't Fix


10
3
Sign in
to vote
Type: Suggestion
ID: 490142
Opened: 9/16/2009 6:22:45 AM
Access Restriction: Public
0
Workaround(s)
view

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]
Details
Sign in to post a comment.
Posted by David Portas 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 Microsoft 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 David Portas 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.
Sign in to post a workaround.