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.

Answer 1

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.

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/