Skip to content
September 4, 2011 / thewiseguy99

‘SELECT INTO’ & Migrating a SQL Server database to SQL Azure

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

During the migration of a large web application to Azure I found some speed bumps. One significant problem is that “SELECT INTO” is not supported.  The existing application made use of stored procedures which I converted but at run-time there were some dynamic SQL statements using “SELECT INTO” I had to migrate as well. For example this statement used “SELECT DISTINCT INTO”,

SELECT DISTINCT p.id INTO #t_base FROM RegionTreeSection rts JOIN SpatialCurrent p ON rts.RegionLocaleCd = … 

the above statement would create the temporary table as well as insert the distinct records from RegionTreeSection into it. This is the dynamic statement that works fine as-is in SQL Server 2000 – 2008 R2 but fails in SQL Azure:

SELECT DISTINCT p.id INTO #t_base
FROM RegionTreeSection rts JOIN SpatialCurrent p ON rts.RegionLocaleCd = p.RegionLocationCd
WHERE rts.SpatialCurrentFk IN (94820) AND ( rts.RegionTreeMapping like ‘@101538@%’ )

the following modifications were required to execute in SQL Azure:

CREATE TABLE #t_base (id INT NOT NULL)

INSERT INTO #t_base (Id)
  SELECT DISTINCT p.id
  FROM RegionTreeSection rts JOIN SpatialCurrent p ON rts.RegionLocaleCd = p.RegionLocationCd
  WHERE rts.SpatialCurrentFk IN (94820) AND ( rts.RegionTreeMapping like ‘@101538@%’ )

This could been converted to use a table variable, however due to some published benchmarks I had concerns about this approach: http://www.sql-server-performance.com/2007/temp-tables-vs-variables/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: