sql-server - How can I execute "for json path" in dqe and insert result of execute in temp table in SQL Server?

Question

DECLARE @QUERY NVARCHAR(MAX) = 'SELECT * FROM Student FOR JSON PATH'
DECLARE @TABLE TABLE(RESULT NVARCHAR(MAX))

INSERT @TABLE
   EXEC (@QUERY)

Error:

The for json clause is not allowed in a INSERT statement

标签： sql-server

Answer 1

you can try this -

DECLARE @TABLE TABLE(RESULT NVARCHAR(MAX))
DECLARE @QUERY NVARCHAR(MAX) = '

;WITH x(a) as 
(
SELECT 
  *
FROM Student  FOR JSON AUTO
)
Select * from x
'
Insert @TABLE
EXEC (@QUERY)
Select * from @TABLE;