Insert data from Json table to the view that has been already created with exec in SQL

Question

I have a problem, I have to insert data from a Json that stored in a table that has jus one field then insert them directly to the view but my code has a error and I can't fix that. My code is this(but it has error):

CREATE PROC [dbo].[viewJson]
AS
BEGIN

DECLARE @OpenJson NVARCHAR(MAX) = (SELECT * FROM FoodSara_tbl FOR JSON AUTO) -- Make Json from Original Table

SELECT * 
FROM OPENJSON(@OpenJson)

WITH (FID INT,
Fname NVARCHAR(50),
Ftype NVARCHAR(20),
Fcount INT,
Fdate VARCHAR(400),
Fregion NVARCHAR(50),
Fdescription NVARCHAR(100)) AS SM


EXEC('CREATE VIEW Json1 AS SELECT * FROM SM')

END

my first table that has Json data:

|  JsonOutPut                                                                   |
|-------------------------------------------------------------------------------|
|[{"FID":1,"Fname":"cake","Fcount":5,"FDate":"2020-02-13","Fregion":"UK"},...]  |

my view should be like this:

| FID | Fname | Fcount| FDate    | Fregion |
|-----|-------|-------|----------|---------|
|  1  | cake  |   5   |2020-02-13|    UK   |

I don't want create new table!

I have an eeror: Incorrect syntax near 'Json1'.

question from:https://stackoverflow.com/questions/65643703/insert-data-from-json-table-to-the-view-that-has-been-already-created-with-exec

Answer 1

Waitting for answers