我想将查询存储为JSON对象.(错误:SELECT INTO语句中不允许使用FOR JSON子句)

Question

CREATE TABLE #T ( DateColumn DATE, SomeColA VARCHAR (20), Temp INT, 
Attribute1 VARCHAR (10), Attribute2 VARCHAR (10) )
INSERT INTO #T VALUES ('20180101', 'A', 8, 'D', NULL)
INSERT INTO #T VALUES ('20180201', 'B', 10, NULL, 'A')
INSERT INTO #T VALUES ('20180301', 'B', 12, NULL, NULL)
INSERT INTO #T VALUES ('20180401', 'A', 14, 'D', 'C')
INSERT INTO #T VALUES ('20180501', 'A', 15, 'E', 'Y')
SELECT DateColumn,  
SomeColA, 
Temp, 
Attribute1, 
Attribute2
-- INTO #NewTable
FROM #T FOR JSON AUTO

现在我已经读了好几页，这是不可能的。然而，这些页面没有包括的是它是如何工作的。

一定很简单，但我找不到。

非常感谢！

Answer 1

JSON

SQL Server (starting with 2016)

https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-服务器-2017

或数据库兼容级别上限为130

使用CTE将查询存储为JSON对象，例如

;WITH x(a) as 
(
SELECT DateColumn,  
SomeColA, 
Temp, 
Attribute1, 
Attribute2
FROM #T FOR JSON AUTO
)
SELECT a INTO #b FROM x

Answer 2

我只是这样做，它会给我json结果。

;WITH x(a) as 
(
SELECT 
PatientId,
LName
FROM tblpatientmaster FOR JSON AUTO
)
--select * from x
SELECT a INTO #temp FROM x
select * from #temp

[｛"PatientId"：1，"LName"："Doe"｝，｛PatientId：10，"LName"："Smith"}，｛"PatientId"：11，"LNname"："Brothers"｝，｛"Hernandez"}，｛"PatientId"：15，"LName"："Velazquez"｝，｛"PatientId"：24，"LName"："Doe"}]

Answer 3

删除名称表中的'#'

示例：

CREATE TABLE #T ( DateColumn DATE, SomeColA VARCHAR (20), Temp INT, 
Attribute1 VARCHAR (10), Attribute2 VARCHAR (10) )

现场演示：http://sqlfiddle.com/#！18/2d6f9/2

输出：

[{"DateColumn":"2018-01-01","SomeColA":"A","Temp":8,"Attribute1":"D"}, 
{"DateColumn":"2018-02-01","SomeColA":"B","Temp":10,"Attribute2":"A"}, 
{"DateColumn":"2018-03-01","SomeColA":"B","Temp":12},{"DateColumn":"2018-04-01","SomeColA":"A","Temp":14,"Attribute1":"D","Attribute2":"C"}, 
{"DateColumn":"2018-05-01","SomeColA":"A","Temp":15,"Attribute1":"E","Attribute2":"Y"}]