SELECT INTO With SQL Azure

  • Article

[This article was contributed by the SQL Azure team.]

SELECT INTO With SQL Azure

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. If you plan to migrate to SQL Azure, you need to modify your code to use table creation instead of the SELECT INTO Statement. This goes for both temporary tables (where clustered indexes are not required) and permanent tables.

Quick query to get some sample data to use:

 CREATE TABLE Source (Id int NOT NULL IDENTITY, [Name] nvarchar(max), [Color] nvarchar(10),  
CONSTRAINT [PK_Source] PRIMARY KEY CLUSTERED 
(
      [Id] ASC
))
      
INSERT INTO Source([Name], [Color]) VALUES ('Shirt','Red')
INSERT INTO Source([Name], [Color]) VALUES ('Pants','Red')
INSERT INTO Source([Name], [Color]) VALUES ('Tie','Pink')

Here is some example code that uses the SELECT INTO statement for a temp table:

 SELECT *
INTO #Destination
FROM Source
WHERE [Color] LIKE 'Red'
-- Do Something

DROP TABLE #Destination

This is going to fail in SQL Azure with this error:

Msg 40510, Level 15, State 2, Line 1

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

To work around this you need to create your destination table then call INSERT INTO. Here is an example:

 CREATE TABLE #Destination (Id int NOT NULL, [Name] nvarchar(max), [Color] nvarchar(10))

INSERT INTO #Destination(Id, [Name], [Color])
SELECT Id, [Name], [Color]
FROM Source
WHERE [Color] LIKE 'Red';

-- Do Something

DROP TABLE #Destination

Summary

Do you have questions, concerns, comments? Post them below and we will try to address them.