準備初始資料習慣用SSMS管理工具在資料庫右鍵[工作]、[產生指令碼],進階選項選擇資料碼類型為僅限資料;但...產生指令碼之後臨時想改資料值時,就悲劇了,要用睡不著數綿羊的絕招。
先前嘗試一種用xml替代初始資料的作法,感覺有方便一點,趕緊筆記下來。
首先建立一個資料表來測試:
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執行時,預設就關閉了。
參考:
nodes() Method (xml Data Type)