Create empty SQL table from existing table in Azure

Question

I would like to create a new empty table by copying the structure of an existing table without copying any of the records. I am using SQL Azure.

Attempt 1:

SELECT * INTO newTable
FROM oldTable
WHERE 1 = 0

Returns error:

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

Attempt 2:

CREATE TABLE newTable
AS (SELECT * FROM oldTable WHERE 1=0)

Returns error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.

Third time lucky? Have I missed something obvious, I am out of ideas.

UPDATE: In the end I performed this procedure:

Added Azure table as linked table in Access mdb
Copied structure-only to local table
Renamed table
Exported to Azure using 'SQL Server Import and Export Wizard'

Andrey Morozov · Accepted Answer · 2014-02-13 13:02:14Z

2

SQL Azure requires that all tables have clustered indexes therefore you can't simply clone table structure without creating indexes.

answered Feb 13, 2014 at 13:02

Andrey Morozov

8,0175 gold badges59 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MikeWo · Accepted Answer · 2014-02-13 13:01:02Z

1

As you've seen SELECT INTO isn't supported in Azure SQL Database. If the destination table already exists, then you can use INSERT INTO to populate it. See Kevin Yu's post on that: http://blog.kevinyu.org/2012/06/workaround-for-select-into-in-sql-azure.html.

I believe the issue of why SELECT INTO isn't supported is because every table in SQL Azure must have a clustered index. The SELECT INTO syntax would produce a table without a clustered index.

This is an open connect issue for this at: http://connect.microsoft.com/SQLServer/feedback/details/776409/add-select-into-to-sql-azure

I do not believe there is a good way to do this without first creating the destination table with a clustered index defined.

answered Feb 13, 2014 at 13:01

MikeWo

11k1 gold badge41 silver badges44 bronze badges

Collectives™ on Stack Overflow

Create empty SQL table from existing table in Azure

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related