史丹利好熱

首先建立一個資料表來測試:

DROP TABLE TblinitData
CREATE TABLE TblinitData
(
C1 INT IDENTITY,
C2 VARCHAR(20),
C3 DATETIME, 
C4 CHAR(10),
C5 INT NULL,
C6 INT NULL,
C7 INT NULL,
C8 INT NULL,
C9 INT NULL,
C10 INT NULL,
C11 INT NULL
)

手動編輯10個欄位值

 

先重現一次以前的方式: 使用SSMS管理工具，工作  > 產生Insert指令碼 >進階 > 要編寫指令碼的資料類型。

產生後的Insert 指令碼:

INSERT [dbo].[TblinitData] 
(
[C1], [C2], [C3], 
[C4], [C5], [C6], [C7], 
[C8], [C9], [C10], [C11]) 
VALUES (1, N'2', CAST(N'1900-01-01 00:00:00.000' AS DateTime), 
N'4         ', 5, 6, 7, 
8, 9, 10, 11)

欄位少還好，但資料表有數十個欄位或是OLAP資料庫內數百個欄位就辛苦了，在VALUES中的參數需要用到睡不著數綿羊的絕招。

 

為了解決這個小麻煩，先將寫入資料用XML格式呈現，然後利用T-SQL解開XML Data，最後Insert語法寫入

1.資料用XML格式呈現資料語法如下，還可以下Where條件，限制想要的初始資料範圍。

  SELECT ... FOR XML AUTO,ELEMENTS

SELECT
	*
FROM TblinitData
FOR XML AUTO, ELEMENTS

2.Parse XML 作為資料來源(利用xml.nodes)並且寫入資料。

DECLARE @XmlData XML;
SET @XmlData = (
'
<TblinitData>
  <C1>1</C1>
  <C2>2</C2>
  <C3>1900-01-01T00:00:00</C3>
  <C4>4         </C4>
  <C5>5</C5>
  <C6>6</C6>
  <C7>7</C7>
  <C8>8</C8>
  <C9>9</C9>
  <C10>10</C10>
  <C11>11</C11>
</TblinitData>
')

INSERT INTO TblinitData
 SELECT 
doc.col.value('C2[1]', 'varchar(20)') C2 ,
doc.col.value('C3[1]', 'datetime') C3 ,
doc.col.value('C4[1]', 'char(10)') C4 ,
doc.col.value('C5[1]', 'int') C5 ,
doc.col.value('C6[1]', 'int') C6 ,
doc.col.value('C7[1]', 'int') C7 ,
doc.col.value('C8[1]', 'int') C8 ,
doc.col.value('C9[1]', 'int') C9 ,
doc.col.value('C10[1]', 'int') C10 ,
doc.col.value('C11[1]', 'int') C11 
 FROM @XmlData.nodes('TblinitData') doc(col)

查詢資料表:

• 有XML Element 框起來的指令碼會比較好改欄位值一點點。
• 據說SQL Server2016會支援JSON，下次就來試看看。
• Connection Option: Set QUOTED_IDENTIFIER On, 才能使用Xml Statement，神奇的是SSMS新增查詢預設都會on，但用sqlcmd執行時，預設就關閉了。

 

參考:

搭配 FOR XML 使用 AUTO 模式

nodes() Method (xml Data Type)