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

Question

I am getting

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

for the below query inside stored procedure

DECLARE @sql NVARCHAR(MAX)
,@sqlSelect NVARCHAR(MAX) = ''
,@sqlFrom NVARCHAR(MAX) = ''
,@sqlTempTable NVARCHAR(MAX) = '#itemSearch'
,@sqlInto NVARCHAR(MAX) = ''
,@params NVARCHAR(MAX)


SET @sqlSelect ='SELECT     
,IT.ITEMNR
,IT.USERNR
,IT.ShopNR
,IT.ITEMID'                 

SET @sqlFrom =' FROM        dbo.ITEM AS IT' 
SET @sqlInto = ' INTO ' + @sqlTempTable + ' ';  

IF (@cityId > 0)
    BEGIN
        SET @sqlFrom = @sqlFrom +
            ' INNER JOIN    dbo.CITY AS CI2
                        ON  CI2.CITYID = @cityId'

        SET @sqlSelect = @sqlSelect +
            'CI2.LATITUDE AS CITYLATITUDE
            ,CI2.LONGITUDE AS CITYLONGITUDE'
    END

SELECT @params =N'@cityId int ' 

SET @sql =  @sqlSelect +@sqlInto +@sqlFrom 

EXEC sp_executesql @sql,@params

I have around 50,000 records, so decided to use Temp Table. But surprised to see this error.

How can i achieve the same in SQL Azure?

Edit: Reading this blog http://blogs.msdn.com/b/sqlazure/archive/2010/05/04/10007212.aspx suggesting us to CREATE a Table inside Stored procedure for storing data instead of Temp table. Is it safe under concurrency? Will it hit performance?

Adding some points taken from http://blog.sqlauthority.com/2011/05/28/sql-server-a-quick-notes-on-sql-azure/

• Each Table must have clustered index. Tables without a clustered index are not supported.
• Each connection can use single database. Multiple database in single transaction is not supported.
• ‘USE DATABASE’ cannot be used in Azure.
• Global Temp Tables (or Temp Objects) are not supported.
• As there is no concept of cross database connection, linked server is not the concept in Azure at this moment.
• SQL Azure is shared environment and because of the same there is no concept of Windows Login.
• Always drop TempDB objects after their need as they create pressure on TempDB.
• During buck insert use batchsize option to limit the number of rows to be inserted. This will limit the usage of Transaction log space.
• Avoid unnecessary usage of grouping or blocking ORDER by operations as they leads to high end memory usage.

Answer 1

SELECT INTO is one of the many things that you can unfortunately not perform in SQL Azure.

What you'd have to do is first create the temporary table, then perform the insert. Something like:

CREATE TABLE #itemSearch (ITEMNR INT, USERNR INT, IT.ShopNR INT, IT.ITEMID INT)
INSERT INTO #itemSearch
SELECT IT.ITEMNR, IT.USERNR, IT.ShopNR ,IT.ITEMID                  
FROM dbo.ITEM AS IT

Answer 2

The new Azure DB Update preview has this problem resolved:

The V12 preview enables you to create a table that has no clustered index. This feature is especially helpful for its support of the T-SQL SELECT...INTO statement which creates a table from a query result.

http://azure.microsoft.com/en-us/documentation/articles/sql-database-preview-whats-new/