SQL Azure doesn't support 'select into' - Is there another way?

Question

I have a very complicated table I'd like to take a temporary backup of whilst I make some changes. Normally, I'd just do the following:

SELECT  *
INTO    temp_User
FROM    dbo.[User] AS u

Unfortunately I'm using Azure, and it appears this isn't supported:

Msg 40510, Level 16, State 1, Line 2 Statement 'SELECT INTO' is not supported in this version of SQL Server.

Is there a way to re-create this feature into a function, potentially? I could do this by scripting the table, creating it and then inserting data using a select statement but given how frequently I use Azure, and how many databases I need to work on in this area this is very unwieldy.

One of sulotions : http://stackoverflow.com/a/34877208/1151741 — Nigrimmist, Jan 19 '16 at 12:51

score 8 · Answer 1 · edited May 23 '17 at 12:15

8

Azure requires a clustered index on all tables, therefore SELECT INTO is not supported.

You'll have to:

CREATE TABLE temp_User () --fill in table structure
INSERT INTO temp_User
SELECT *
FROM dbo.[User]

To script table easily you can write your own or use one of the answers to this question:

Script CREATE Table SQL Server

Update: As Jordan B pointed out, V12 will include support for heaps (no clustered index requirement) which means SELECT INTO will work. At the moment V12 Preview is available, Microsoft of course only recommends upgrading with test databases.

edited May 23 '17 at 12:15

Community

1
1

answered Sep 15 '13 at 23:12

Hart CO

34,064
6
48
63

Sure, I understand this and mentioned it in my original question - but is there a way to re-create this process (perhaps using system tables?) – Michael A Sep 15 '13 at 23:20

score 5 · Accepted Answer · answered Jan 13 '15 at 22:14

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/

score 1 · Answer 3 · answered Mar 31 '14 at 02:41

Unfortunately it cant be done. Here is how I worked around it:

Open SQL Server Management Studio
Right click on the table
Select Script as ... Create Table
Edit the generated script to change the table name to what you specified in your query
Execute your query

TGH · Answer 4 · 2013-09-15T23:21:07.043

0

INSERT INTO temp_User
SELECT * FROM dbo.[User]

You can try the above. It's basically a select that is applied to an insert statement

http://blog.sqlauthority.com/2011/08/10/sql-server-use-insert-into-select-instead-of-cursor/

edited Sep 15 '13 at 23:21

answered Sep 15 '13 at 23:11

TGH

38,769
12
102
135

Syntactically incorrect? And I don't think you meant insert into? – Michael A Sep 15 '13 at 23:19

score 0 · Answer 5 · answered Jan 31 '15 at 23:03

Lets assume you have a table with Id, Column1 and Column2. Then this could be your solution

CREATE TABLE YourTableName_TMP ....
GO
SET IDENTITY_INSERT YourTableName_TMP ON 
GO
INSERT INTO YourTableName_TMP
([Id] ,[Column1] ,[Column2])      
SELECT [Id] ,[Column1] ,[Column2]
FROM 
(
    SELECT * 
    FROM
    (
        SELECT  [Id] ,[Column1] ,[Column2] ROW_NUMBER() OVER(ORDER BY ID DESC) AS RowNum
        FROM YourTableName
    )
    WHERE RowNum BETWEEN 0 AND 500000
)
GO
SET IDENTITY_INSERT YourTableName_TMP OFF
GO

First you create a temporary table and then you insert rows windowed. It's a mess, I know. My experiences are, that executing this using SQL Server Management Studio from a client makes approximately 200.000 rows a minute.

score 0 · Answer 6 · answered Jan 19 '16 at 12:46

As wrote above - you need to rewrite your query from using select into to create table like

It is my sample. Was :

        select  emrID, displayName --select into
        into #tTable
        from emrs

        declare @emrid int
        declare @counter int = 1
        declare @displayName nvarchar(max)

        while exists (select * from #tTable)
        begin
            -- some business logic
            select top 1  @displayName = displayname
            from #tTable
            group by displayname
            update emrs  set groupId = @counter where @displayName = displayname    
            delete #tTable
            where @displayName = displayname
            set @counter = @counter + 1 
        end
        drop table #tTable

Modified :

        CREATE TABLE #tTable ([displayName] nvarchar(max)) --create table
        INSERT INTO #tTable -- insert to next select  :

        select  displayName            
        from emrs

        declare @emrid int
        declare @counter int = 1
        declare @displayName nvarchar(max)

        while exists (select * from #tTable)
        begin
            -- some business logic
            select top 1  @displayName = t.displayName
            from #tTable as t
            group by t.displayname
            update emrs  set groupId = @counter where @displayName = displayname    
            delete #tTable
            where @displayName = displayname
            set @counter = @counter + 1 
        end
        drop table #tTable

Do not forget to drop your temp table. Also, you can find more simple example with description here : http://www.dnnsoftware.com/wiki/statement-select-into-is-not-supported-in-this-version-of-sql-server

SQL Azure doesn't support 'select into' - Is there another way?

6 Answers6

Linked