SELECT INTO With SQL Azure - SQL Azure Team Blog - Site Home - MSDN Blogs

SELECT INTO With SQL Azure

SELECT INTO With SQL Azure

Rate This
  • Comments 12

SELECT INTO With SQL Azure

SQL Azure requires that all tables have clustered indexes therefore SELECT INTO statements, which creates a table and does not support the creation of clustered indexes. If you plan to migrate to SQL Azure, you need to modify your code to use table creation instead of the SELECT INTO Statement. This goes for both temporary tables (where clustered indexes are not required) and permanent tables.

Quick query to get some sample data to use:

CREATE TABLE Source (Id int NOT NULL IDENTITY, [Name] nvarchar(max), [Color] nvarchar(10),  
CONSTRAINT [PK_Source] PRIMARY KEY CLUSTERED 
(
      [Id] ASC
))
      
INSERT INTO Source([Name], [Color]) VALUES ('Shirt','Red')
INSERT INTO Source([Name], [Color]) VALUES ('Pants','Red')
INSERT INTO Source([Name], [Color]) VALUES ('Tie','Pink')

Here is some example code that uses the SELECT INTO statement for a temp table:

SELECT *
INTO #Destination
FROM Source
WHERE [Color] LIKE 'Red'
-- Do Something

DROP TABLE #Destination

This is going to fail in SQL Azure with this error:

Msg 40510, Level 15, State 2, Line 1

Statement 'SELECT INTO' is not supported in this version of SQL Server.

To work around this you need to create your destination table then call INSERT INTO. Here is an example:

CREATE TABLE #Destination (Id int NOT NULL, [Name] nvarchar(max), [Color] nvarchar(10))

INSERT INTO #Destination(Id, [Name], [Color])
SELECT Id, [Name], [Color]
FROM Source
WHERE [Color] LIKE 'Red';

-- Do Something

DROP TABLE #Destination

Summary

Do you have questions, concerns, comments? Post them below and we will try to address them.

  • Why does SQL Azure include this new restriction that all permanent tables have a clustered index?

    Is there some change to the underlying architecture that forces such a constraint? I can't imagine breaking compatibility with SQL Server in such a way without some substantial cost benefit ?

  • curiousdbguy,

    Its because every table is replicated to multiple places (for HA) and they can't do that unless there is an index on the table.

    Anyway, I digress...

    David, there is a crying need for a T-SQL construct that enables us to create a table on-the-fly using a SQL statement but which also allows us to define constraints. I reckon:

    CREATE TABLE

    AS <sql-statement>

    CONSTRAINTS <constraint-list>

    should do it!

    This has been on Connect for eons (https://connect.microsoft.com/SQLServer/feedback/details/490142/new-version-of-select-into-that-supports-indexes). Please push to get it into Denali/SQL Azure.

    -Jamie

  • Curious Guy:  Today's blog post should answer your question.

    http://blogs.msdn.com/sqlazure/archive/2010/05/12/10011257.aspx

  • Hi,

    this explains some but not all. Why can't you make select into a temp table since a temp table doesn't require a clustered index?

    In our case select into are almost entirely used in temp table situations.

    / Andreas

  • Hi,

    In case of inserting more than 30,000 records from XML string, 'insert into' statement took more time where

    'Select into' completes within a second.

    It would be better if you would allow 'Select into' statements in SQL Azure.

  • SQL 2008 R2 will crash/fail with some situations with insert + select from/into #temp table. without clusered index

  • These limitations drive me insane and it makes more work for me.  Between the limitations and workarounds in both Data Dude and SQL Azure,  I feel like I am constantly fighting against the technology MS provides.  

  • I know this is an old feed, but this is maddening.  The workarounds only work IF you know what the column names are going to be ahead of time.

  • Sql Azure is for small web projects. Im going crazy trying to implement complex business logic. If im not fighting Sql Azures limited features im fighting the query optimizer, where the workarounds involve the features azure do not have.

    Now i have to sit and write out 5 wide temp tables because Azure wont let me use SELECT into and the query optimizer is to stupid to handle complex expression and CTE's.

  • I agree with SqlServerHairDay - Azure simply has too many crazy anomalies for coding anything more complex than hairdressing salon appointments. The inability to create temp dynamic tables is a massive faux pas, which must affect the vast majority of business systems. I'm looking elsewhere - any ideas anyone?

  • I'll never create a wp website using Windows Azure again. I can't believe I pay the money I do for Azure not to include this basic functionality. Microsoft let me down. Time to get rid of Azure and Visual studios.

  • This is indeed a disappointing limitation, especially for temp tables where the clustered index logic doesn't play.

Page 1 of 1 (12 items)
Leave a Comment
  • Please add 8 and 1 and type the answer here:
  • Post