SQL Server: Dynamically create Column Names from JSON Auto Data

I created JSON Data with JSONAUTO in SQL Server.

   SELECT *
   from #Testdata
   FOR JSON AUTO

Its stored in nvarchar(max), and create multiple records of the same type.

[{"ProductId":1,"ProductName":"Food","ProductDescription":"Apple","DatePurchased":"1995-05-01T00:00:00"},   {"ProductId":2,"ProductName":"Electronics","ProductDescription":"TV","DatePurchase":"2018-09-17T00:00:00"}]

Now I want to insert into Tablename with Exact same Column names as Json Fields. Trying to Dynamically create the column names, to insert into table with exact same column names. How would I conduct this with SQL Server Json library?

insert into dbo.SampleTable
(
      ProductId,
      ProductName, 
      ProductDescription, 
      DatePurchased
)

Now allowed to Explicity state the column numes, from typing, column names must be derived from Json data itself. Will have future types of data later.

Update:

Reading this answer, wondering if there is a way to bypass removing first and last letter, doesn't SQL server have an internal library function? This is what I am looking for, while eliminating the substring stuff, which is removing brackets [ and ].

SQL Server - Using JSON to return Column Names

declare @json NVARCHAR(MAX)  = '[{"ProductId":1,"ProductName":"Food","ProductDescription":"Apple","DatePurchased":"1995-05-01T00:00:00"},   {"ProductId":2,"ProductName":"Electronics","ProductDescription":"TV","DatePurchase":"2018-09-17T00:00:00"}]'

DECLARE @jsonformatted NVARCHAR(MAX) =  substring(@json, 2, len(@json)-1)
SELECT * FROM OPENJSON(@jsonformatted);

Solution

    DECLARE @jsonata NVARCHAR(MAX)='[{"ProductId":1,"ProductName":"Food","ProductDescription":"Apple","DatePurchase":"1995-05-01T00:00:00"},   {"ProductId":2,"ProductName":"Electronics","ProductDescription":"TV","DatePurchase":"2018-09-17T00:00:00"}]'

DECLARE @Query NVARCHAR(MAX)=N' INSERT INTO [TableName] ('

SELECT @Query+=T.ColumnName+','
FROM
(    
SELECT   y.[key] AS ColumnName,
         x.[Key]                                    
FROM OPENJSON(@jsonata) x
CROSS APPLY (SELECT * FROM OPENJSON(x.[value])) y
) AS T
WHERE T.[Key]=0


SET @Query=STUFF(@Query,LEN(@Query),1,' ')+' ) VALUES ('


SELECT @Query+=''''+T.Value+''''+
       (CASE WHEN ISNULL(LEAD(T.[Key]) OVER(ORDER BY T.[Key]),0)=T.[Key] THEN ',' ELSE '),(' END) 
FROM
(
SELECT   y.[key] AS ColumnName,
         y.[Value],
         X.[key]
FROM OPENJSON(@jsonata) x
CROSS APPLY (SELECT * FROM OPENJSON(x.[value])) y
) AS T

SET @Query=SUBSTRING(@Query,1,LEN(@Query)-2)

--Print Query Statment
PRINT (@Query)

EXEC(@Query)

  [1]: https://i.sstatic.net/KpNKc.png