[SQL Server]Insert資料指令碼(JSON in SQL Server 2016)

先前嘗試用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.查詢資料表: 成功寫入。

Import/Export Json Data 示意圖:

Overview of built-in JSON support

 

小結:

  • SQL Server 2016儲存JSON 的Data Type會使用NVARCHAR,這點和XML有XML的Data Type有的不同。
  • JSON是網站前後台資料交換或是跨網站交換時經常會使用的格式,同時也很適合儲存非結構的資料,下一次重構系統多了一種解決方案。
  • 可以用ISJSON(@jsonData) 判斷資料是否符合JSON格式。1:符合 0:不符合
  • 下次來試看看BulkInsert from Json file

 

參考:

JSON Data (SQL Server)

BulkInsert from Json file

Query JSON Data with Built-in Functions