先前嘗試用xml格式替代匯出及匯入初始資料的作法,感覺有順手點,SQL Server 2016開始支援JSON,
剛好手邊有Azure帳號,早上下雨不能跑步,就來測試JSON。
- Convert SQL Server data to JSON or export JSON
- Convert JSON to rows and columns or import JSON To SQL Server
1.首先建立一個資料表來測試:
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
)
2.使用原本產生1筆資料的指令碼寫入1筆測試資料:
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)
3.查詢資料表內容:
4.試著模仿SELECT ... FOR XML AUTO,ELEMENTS把資料用JSON格式匯出
SELECT * FROM [dbo].[TblinitData]
FOR JSON AUTO;
JSON Format
5.Parse JSON 作為資料來源匯入(利用OPEN.JSON)並且寫入資料(這次想要額外寫入2筆)。
可以注意到多筆資料列時,是用逗號,分隔。
DECLARE @jsonData NVARCHAR(MAX) = N'
{"TblinitDasta":
[
{"C1":1,
"C2":"2",
"C3":"1900-01-01T00:00:00",
"C4":"first ",
"C5":5,
"C6":6,
"C7":7,
"C8":8,
"C9":9,
"C10":10,
"C11":11},
{"C1":2,
"C2":"2",
"C3":"1900-01-01T00:00:00",
"C4":"second ",
"C5":5,
"C6":6,
"C7":7,
"C8":8,
"C9":9,
"C10":10,
"C11":11}
]
}
';
INSERT INTO [dbo].[TblinitData]
SELECT * FROM OPENJSON(@jsonData,'$.TblinitDasta')
WITH(C2 varchar(20) '$.C2',
C3 datetime '$.C3',
C4 CHAR(10) '$.C4',
C5 INT '$.C5',
C6 INT '$.C6',
C7 INT '$.C7',
C8 INT '$.C8',
C9 INT '$.C9',
C10 INT '$.C10',
C11 INT '$.C11'
);
6.查詢資料表: 成功寫入。
小結:
- SQL Server 2016儲存JSON 的Data Type會使用NVARCHAR,這點和XML有XML的Data Type有的不同。
- JSON是網站前後台資料交換或是跨網站交換時經常會使用的格式,同時也很適合儲存非結構的資料,下一次重構系統多了一種解決方案。
- 可以用ISJSON(@jsonData) 判斷資料是否符合JSON格式。1:符合 0:不符合
- 下次來試看看BulkInsert from Json file
參考:
Query JSON Data with Built-in Functions