I want to insert this data into multiple tables from Json file into Sql table with their relation (foreign key) and link all data to one master table

Question

Database will be having identity linked to each level of SQL. Also would like to merge data of all three table into one master table Table_master which will have final number of data based on inner element record id 2.

expected to update data into table of SQL Server 2016 Table structure with use of Foreign key Also how to export same data into Json back again in a shared network drive folder.

Expected data to be updated in Table

Repro Code:

IF OBJECT_ID('dbo.Table1') IS NULL
    CREATE TABLE dbo.Table1
    (
    Name VARCHAR(100), Location VARCHAR(100), Region VARCHAR(100) 
    )


DECLARE @MYJSON VARCHAR(1000)

SET @MYJSON = '
{
  "Name":"ABC",
  "Location":"East US",
  "Region":"West US",
  "Element":
 [
     {  
        "Name":"IE1",
        "Description":"IE1 Description",
        "Type":"Small",
        "InnerElement":
        [ 
           { "Key":"Name", "Value":"IME1"},
           {"Key":"AnotherProperty","Value":"Value1"}
        ]
     },
     {  
        "Name":"IE2",
        "Description":"IE2 Description",
        "Type":"Medium",
        "InnerElement":
        [ 
           {"Key":"Name","Value":"IME2"},
           {"Key":"Address","Value":"Xyz"}, 
           {"Key":"Type","Value":"Simple"},
           {"Key":"LastProperty","Value":"ValueX"}
        ]
     }
 ]}
 '

SELECT 
JSON_VALUE(@MYJSON,'$.Name') As Name,
JSON_VALUE(@MYJSON,'$.Location') As Location,
JSON_VALUE(@MYJSON,'$.Region') As Region

Answer 1

USE JSON_CAT GO DECLARE @JsonObject NVARCHAR(MAX) 
SELECT @JsonObject=BulkColumn from  openrowset  (BULK 'C:\Users\uverma\Desktop\Test.Json, SINGLE_CLOB)T 
BEGIN TRANSACTION;
INSERT Table1([Name], [Location], [Region])
SELECT [Name], [Location], [Region]
FROM OPENJSON(@JsonObject, '$.InnerElement')
WITH (    [Name] VARCHAR(100),    [Location] VARCHAR(100),    [Region] VARCHAR(100));
DECLARE @Table1Id INT = SCOPE_IDENTITY();
DECLARE @Table3Input TABLE([Table2Id] INT, [InnerMostElement] NVARCHAR(MAX));
MERGE Table2
USING (    SELECT [Name], [Description], [Type], [InnerMostElement]    
FROM OPENJSON(@JsonObject, '$.InnerElement')    
WITH (        [Name] VARCHAR(100),        [Description] VARCHAR(100),        [Type] VARCHAR(100),        [InnerMostElement] NVARCHAR(MAX) AS JSON    )) AS J 
ON 1 = 0    
-- Always INSERT
WHEN NOT MATCHED THEN     
INSERT([Table1Id], [Name], [Description], [Type])    
VALUES (@Table1Id, J.[Name], J.[Description], J.[Type])    
OUTPUT inserted.Id, J.[InnerMostElement]    
INTO @Table3Input([Table2Id], [InnerMostElement]);  

INSERT Table3([Table2Id], [Key], [Value]) 
SELECT [Table2Id], KV.[Key], KV.[Value] 
FROM @Table3Input 
CROSS APPLY (    SELECT [Key], [Value]    
FROM OPENJSON([InnerMostElement])    
WITH (        [Key] VARCHAR(100),[Value] VARCHAR(100))) AS KV;

COMMIT;

Please see I have tried with this and my data is importing as well but value inserting in database are becoming null