Replacement for "select * into" in sql azure?

Question

I need a way to generically take a table and copy its data into a new table--basically the same thing that SELECT * INTO does in regular SQL Server. Is there a way to do this in SQL Azure? I only have the existing and new table names at this point.

Answer 1

I encountered the same problem and the author's answer is not very detailed, so I will give some more information, on how i solved it.

I needed to duplicate tables that start with a given prefix ('from_') into new tables with prefix ('to_').

Generate CREATE Statement

I use this query (found on stackoverflow) to generate all CREATE statements, for every table that starts with 'from_' prefix.

select  'create table [' + so.name + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END as query
OBJECTPROPERTY(object_id(TABLE_NAME), 'TableHasIdentity') as tablehasidentity
from    sysobjects so
cross apply
    (SELECT 
        '  ['+column_name+'] ' + 
        data_type + case data_type
            when 'sql_variant' then ''
            when 'text' then ''
            when 'ntext' then ''
            when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
            else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +
        case when exists ( 
        select id from syscolumns
        where object_name(id)=so.name
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
        ) then
        'IDENTITY(' + 
        cast(ident_seed(so.name) as varchar) + ',' + 
        cast(ident_incr(so.name) as varchar) + ')'
        else ''
        end + ' ' +
         (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
          case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', ' 

     from information_schema.columns where table_name = so.name
     order by ordinal_position
    FOR XML PATH('')) o (list)
left join
    information_schema.table_constraints tc
on  tc.Table_name       = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'
cross apply
    (select '[' + Column_Name + '], '
     FROM   information_schema.key_column_usage kcu
     WHERE  kcu.Constraint_Name = tc.Constraint_Name
     ORDER BY
        ORDINAL_POSITION
     FOR XML PATH('')) j (list)
where   xtype = 'U'
AND name    NOT IN ('dtproperties') AND name like 'from_%'

This query results in a set of values:

['query'] = create table [from_users_roles] (  [uid] int  NOT NULL DEFAULT ((0)),   [rid] int  NOT NULL DEFAULT ((0)), )ALTER TABLE from_users_roles ADD CONSTRAINT from_users_roles_pkey PRIMARY KEY  ([uid], [rid])
['tablehasidentity'] = 1 or 0

Now replace the prefixes in the query 'from_' with 'to_' and the CREATE Statement is finished:

create table [to_users_roles] (  [uid] int  NOT NULL DEFAULT ((0)),   [rid] int  NOT NULL DEFAULT ((0)), )ALTER TABLE to_users_roles ADD CONSTRAINT to_users_roles_pkey PRIMARY KEY  ([uid], [rid]);

Create INSERT Statement

When you want to insert data from one table to another, you have to distinguish between two cases:

TablehasIdentity == 0

INSERT INTO to_users_roles SELECT * FROM from_users_roles

TablehasIdentity == 1

This case is a bit more complex. The statement requires a column list and IDENTITY_INSERT switched on.

DECLARE @Query nvarchar(4000)
DECLARE @columnlist nvarchar(4000)

// Result of this query e.g.: "[cid], [pid], [nid], [uid], [subject]"
SET @columnlist = (SELECT SUBSTRING((SELECT ', ' + QUOTENAME(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS  WHERE TABLE_NAME = 'from_users_roles' ORDER BY ORDINAL_POSITION FOR XML path('')), 3,  200000))

SET @query ='SET IDENTITY_INSERT to_users_roles ON; INSERT INTO to_users_roles (' + @columnlist + ')  SELECT  ' + @columnlist + ' FROM from_users_roles; SET IDENTITY_INSERT to_users_roles OFF'
exec sp_executesql @query;

This worked out for me pretty well.

Answer 2

The latest version of Azure SQL DB, now in Preview, supports the SELECT INTO syntax and no longer requires a clustered index. For a detailed description of its features, and how to use it, see http://azure.microsoft.com/en-us/documentation/articles/sql-database-preview-whats-new/

Answer 3

After doing more research, it looks like there is no simple way to do this. You basically have to read the table's schema information and create the new table based on that.

Answer 4

Select into is now supported SQL DB V12. Just upgrade your server and start using the syntax.

Answer 5

I found a clever trick on this blog

Instead of using "select into" use "insert select".

First you have to create the destination table. To do this, right click on the source table in SQL Management Studio, and choose "Script Table as" -> "Create To" -> "New Query Window".

Then, change the name of the table in the query, and execute the query. Below is an example where I have added today's date to the new table, calling it "Entities_2015_08_24" (the old table was called "Entities"):

CREATE TABLE [dbo].[Entities_2015_08_24](
    [Url] [nvarchar](max) NULL,
    [ClientID] [nvarchar](max) NULL
)

Then, do a "insert select" from the old table (Entities) into the new table (Entities_2015_08_24):

INSERT INTO [dbo].[Entities_2015_08_24]
           ([Url]
           ,[ClientID]
           )
SELECT 
       [Url]
      ,[ClientID]
  FROM [dbo].[Entities]

Answer 6

Q: Did you try it?
Q: Did you look at the SQL Azure documentation

ADDENDUM

AFAIK, you cannot use select into syntax to "clone" a table in Azure SQL. Because Azure requires a clustered index, and select into has no provision for defining one.

Details, and a potential workaround, are here:

http://blogs.msdn.com/b/windowsazure/archive/2010/05/04/select-into-with-sql-azure.aspx