積沙成塔

【情境描述】

當您在 Windows Azure SQL Database（以下簡稱 SQL Database）中使用下列的 T-SQL 指令碼，嘗試複製 ClientBasic 資料表的內容到 ClientBasic_1011130：

select * into ClientBasic_1011130

from ClientBasic

您會遇到下列的錯誤訊息：

在 SQL Database 並不完全支援 SQL Server 所有的 T-SQL 功能，而發生這個問題的原因正是 SQL Database 不支援 SELECT INTO 敘述所致。

【實作步驟】

使用 SELECT INTO 最常見的用法就是用來快速複製來源資料表的結構（不包含索引與 Primary Key），偏偏 SQL Database 不支援這樣的寫法，因此筆者使用下列的指令碼來嘗試複製來源資料表的結構：

   1:  --建立儲存來源資料表 MetaData 的資料表變數

   2:  declare @MetaData table

   3:  ( 

   4:      is_hidden bit not null,

   5:      column_ordinal int not null,

   6:      name sysname NULL,

   7:      is_nullable bit NOT NULL,

   8:      system_type_id int NOT NULL,

   9:      system_type_name nvarchar(256) NULL,

  10:      max_length smallint NOT NULL,

  11:      precision tinyint NOT NULL,

  12:      scale tinyint NOT NULL,

  13:      collation_name sysname NULL,

  14:      user_type_id  int NULL,

  15:      user_type_database sysname NULL,

  16:      user_type_schema sysname NULL,

  17:      user_type_name sysname NULL,

  18:      assembly_qualified_type_name nvarchar(4000),

  19:      xml_collection_id int NULL,

  20:      xml_collection_database sysname NULL,

  21:      xml_collection_schema  sysname NULL,  

  22:      xml_collection_name  sysname NULL,  

  23:      is_xml_document  bit NOT NULL,  

  24:      is_case_sensitive  bit NOT NULL,  

  25:      is_fixed_length_clr_type  bit NOT NULL,

  26:      source_server sysname  NULL, 

  27:      source_database  sysname NULL,

  28:      source_schema  sysname NULL,

  29:      source_table sysname NULL, 

  30:      source_column  sysname NULL,

  31:      is_identity_column  bit NULL,  

  32:      is_part_of_unique_key bit NULL,

  33:      is_updateable bit NULL,  

  34:      is_computed_column bit NULL,  

  35:      is_sparse_column_set bit NULL,  

  36:      ordinal_in_order_by_list  smallint NULL,  

  37:      order_by_list_length smallint NULL,  

  38:      order_by_is_descending smallint NULL,  

  39:      tds_type_id int NOT NULL,  

  40:      tds_length int NOT NULL,  

  41:      tds_collation_id int NULL,  

  42:      tds_collation_sort_id tinyint NULL

  43:  )    

  44:  

  45:  --準備原始資料表的欄位及資料型態

  46:  declare @name sysname                                    --欄位名稱

  47:              ,@system_type_name nvarchar(256)           --欄位資料型態

  48:              ,@sql nvarchar(max) = ''                     --動態建立暫存資料表的SQL字串

  49:  

  50:  --利用 sp_describe_first_result_set 系統預存程序取得來源資料表的 MetaData

  51:  insert into @MetaData

  52:  exec sp_describe_first_result_set N'select * from ClientBasic'

  53:  

  54:  --利用cursor取得組合建立暫存資料表的欄位

  55:  declare cur_meta_data cursor for

  56:  select name,system_type_name

  57:  from @MetaData

  58:  

  59:  open cur_meta_data

  60:  fetch next from cur_meta_data into @name,@system_type_name

  61:  

  62:  while (@@fetch_status = 0)

  63:  begin

  64:      set @sql += iif(@sql = '',@name + ' ' + @system_type_name,' , ' + @name + ' ' + @system_type_name)

  65:      fetch next from cur_meta_data into @name,@system_type_name

  66:  end

  67:  

  68:  close cur_meta_data

  69:  deallocate cur_meta_data

  70:  

  71:  --建立暫存資料表

  72:  set @sql = 'if object_id(''TmpTab'') is not null ' + 

  73:                     ' drop table TmpTab ' +

  74:                     'create table TmpTab (' + @sql + ')'

  75:  exec (@sql)

在上述指令碼中利用 sp_describe_first_result_set 系統預存程序取得查詢結果的 MetaData，接著將 MetaData 的內容 INSERT 到與 sp_describe_first_result_set 回傳結果集相同的資料表變數，最後利用 Cursor 來組合來源資料表的資料行定義，並搭配 EXEC 敘述來執行動態 T-SQL，進而做到類似 SELECT INTO 複製資料表結構的目的。

光是這樣做還不夠，因為 SQL Database 中的資料表必須都有叢集索引（Clustered Index），因此再利用下列指令碼來取得來源資料表的叢集索引欄位，同樣的使用動態 T-SQL 搭配 EXEC 敘述來建立叢集索引。

   1:  declare @clustered_index nvarchar(50) 

   2:  set @clustered_index = (

   3:                          select b.name

   4:                          from sys.index_columns a

   5:                          join sys.columns b

   6:                          on b.object_id = a.object_id

   7:                             and a.column_id = b.column_id

   8:                          join sys.indexes c

   9:                          on a.object_id = c.object_id

  10:                             and c.type = 1

  11:                          where a.object_id = object_id(N'ClientBasic','U')

  12:                         )

  13:  

  14:  set @sql =  N'create clustered index clusteredidx on TmpTab(' + @clustered_index + N')'

  15:  

  16:  exec (@sql)

最後您就可以使用 INSERT INTO .. FROM 敘述來複製資料表的內容，如下列的指令碼：

insert into TmpTab

select * from ClientBasic

select *

from TmpTab

經過上述步驟，您就可以利用變通的方式來達到與 SELECT INTO 相同目的，快速複製來源資料表的內容到暫存資料表。

【參考資料】

- SQL Server 2012 的 T-SQL 新功能–Metadata 探索

- sp_describe_first_result_set (Transact-SQL)