I want to store a query as a JSON object. (Error: The FOR JSON clause is not allowed in a SELECT INTO statement.)

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

Now I've read on several pages that this is not possible. What those pages do not include however, is how this does work.

Must be quite simple, but I can't find it.

Thanks a lot!

Answer 1

JSON

SQL Server (starting with 2016)

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

or upper db compatibility level to 130

Store a query as a JSON object use CTE, for example

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