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.

One from solutions : http://stackoverflow.com/a/34877208/1151741 — Nigrimmist, Jan 19 '16 at 12:52

score 7 · Answer 1 · answered Mar 12 '13 at 11:42

7

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

answered Mar 12 '13 at 11:42

Mathew Thompson

55,877
15
127
148

From the blog i referred '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'. In this case I need to create a primary key. Is it needed? – Murali Murugesan Mar 12 '13 at 11:45
Is there a list of unfortunate items for SQL Azure? It will be helpful for me. So i could avoid mistakes at the beginning itself – Murali Murugesan Mar 12 '13 at 11:47
1

@Murali In the case of temporary tables, that's not needed. As for that list, you can find it here http://msdn.microsoft.com/en-us/library/windowsazure/ee336253.aspx – Mathew Thompson Mar 12 '13 at 11:49
The no of columns are not static, it vary based on condition. I am using dynamic query. I have updated my question. In this case How can i build table with dynamic column and INSERT Statement? – Murali Murugesan Mar 13 '13 at 05:32

score 5 · Answer 2 · answered Jan 14 '15 at 01:10

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/

David S. · Answer 3 · 2013-03-12T11:46:14.033

2

Create the table using # prefix, e.g. create table #itemsearch then use insert into. The scope of the temp table is limited to the session so there will no concurrency problems.

edited Mar 12 '13 at 11:46

answered Mar 12 '13 at 11:39

David S.

5,965
2
40
77

Create table is same like Temp table? Is it safe? – Murali Murugesan Mar 12 '13 at 11:40
1

You need to use the `#` prefix when you create the table to tell the server that it is a temporary table. E.g. `create table #itemsearch`. It's good practice to drop the table anyway after you're done, though. If you create a table without the `#` you will probably have concurrency problems.. – David S. Mar 12 '13 at 11:43
The no of columns are not static, it vary based on condition. I am using dynamic query. I have updated my question. In this case How can i build table with dynamic column and INSERT Statement? – Murali Murugesan Mar 13 '13 at 05:33

score -1 · Answer 4 · answered Aug 01 '15 at 06:40

Well, As we all know SQL Azure table must have a clustered index, that is why SELECT INTO failure copy data from one table in to another table. If you want to migrate, you must create a table first with same structure and then execute INSERT INTO statement. For temporary table which followed by # you don't need to create Index.

how to create index and how to execute insert into for temp table?

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

4 Answers4