SELECT INTO statement not supported in this version of SQL Server - SQL Azure

Question

I get

SELECT INTO statement is not supported in this version of SQL Server in SQL Server

for the request below inside the 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 about 50,000 records, so I decided to use the Temp Table. But surprised to see this error.

How can I achieve the same in SQL Azure?

Edit: Reading this blog is http://blogs.msdn.com/b/sqlazure/archive/2010/05/04/10007212.aspx , asking us to CREATE a table inside a stored procedure to store data instead of a Temp table. Is it safe with concurrency? Will performance hit?

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

• Each table must have a clustered index. Tables without a clustered index are not supported.
• Each connection can use one database. Multiple databases in a single transaction are not supported.
• 'USE DATABASE cannot be used in Azure.
• Global Temp tables (or Temp Objects) are not supported.
• Since there is no concept of connecting to cross databases, a linked server is not a concept in Azure at this time.
• SQL Azure is a common environment, and because of this, there is no concept of Windows login.
• Always discard TempDB objects after they are needed, as they create pressure on TempDB.
• When inserting buck, use the batchsize parameter to limit the number of rows inserted. This will limit the use of transaction log space.
• Avoid unnecessarily using grouping or blocking ORDER operations, as they result in high memory usage.

Answer 1

SELECT INTO is one of many things that you, unfortunately, cannot do in SQL Azure.

What you need to do is first create a temporary table and 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 version of Azure DB Update Viewer fixes this problem:

Preview V12 allows you to create a table without clustering the index. This function is especially useful for supporting T-SQL SELECT ... INTO, which creates a table from a query result.

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

Answer 3

Create a table using the # prefix, for example. create table #itemsearch , then use insert into . The size of the temp table is limited by the session, so there will be no problems with concurrency.

Answer 4

Well, as we all know, an Azure SQL table must have a clustered index, so SELECT INTO fails copying data from one table to another table. If you want to perform the migration, you must first create a table with the same structure, and then execute the INSERT INTO statement. For a temporary table followed by #, you do not need to create an index.

how to create an index and how to insert into temp table?