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

2016-03-12

9095
0
SQL Server

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