CODEWITHSUNDEEP
sqlsql-server
Ali
Ali

Reputation: 45

Inserting records in 1 to many table

I have two tables (1 to many relationships)

In my application, I am receiving 100+ records to insert in table 1 (via JSON - Web API C#) and with every record in table 1. I have to enter 7 records in table 2.

For example, If I get 100 records in table 1 then table2 will have 100*7 = 700 records

I have a stored procedure that is getting table type as a parameter and I am using Insert into select query to insert records in table one. Note table 1 has an IDENTITY column. So, I am struggling with how to insert records in table2.

Note: Table1 Id is IDENTITY

Note: I can't use SCOPE_IDENTITY because in my case I am inserting many records in table 1 using an Insert into select query

Please advice.

Upvotes: 1

Views: 194

Answers (2)

Yair Maron
Yair Maron

Reputation: 1948

OK, so you have a JSON holding items for Table1 and sub items for Table2, which is in a relation with Table1.

So I suggest creating temporary tables to extract the JSON key for each of them. And then use MERGE to insert and get all the new Ids linked to the JSON key.

Here's how you can do this:

Let's say you have a @json parameter in your stored procedure, and your JSON format is in a "parent-child" structure, for example:

@json AS NVARCHAR(MAX) =
'[{
    "parent_field1": 11,
    "parent_field2": "t1v1",
    "children": [
        {"child_field1": 21, "child_field2": "t2v1"},
        {"child_field1": 22, "child_field2": "t2v2"}
    ]
},
{
    "parent_field1": 12,
    "parent_field2": "t1v2",
    "children": [
        {"child_field1": 23, "child_field2": "t2v3"},
        {"child_field1": 24, "child_field2": "t2v4"}
    ]
}]';

So your procedure should be as followed (steps 1 to 4).

(1) Extracting Parent Rows from JSON

-----[ Get Parent Key and Values ]-----
SELECT  parent.[key]                                AS  ParentKey,
        JSON_VALUE(parent.value, '$.parent_field1') AS  ParentField1,
        JSON_VALUE(parent.value, '$.parent_field2') AS  ParentField2
INTO    #Parents
FROM    OPENJSON(@json, '$') AS parent

According to the JSON example, this will be inserted into #Parents:

ParentKey ParentField1 ParentField2
0 11 t1v1
1 12 t1v2

(2) Extracting Child Rows from JSON

-----[ Get Children Keys and Values ]-----
SELECT  parent.[key]                                AS  ParentKey,
        child.[key]                                 AS  ChildKey,
        JSON_VALUE(child.value, '$.child_field1')   AS  ChildField1,
        JSON_VALUE(child.value, '$.child_field2')   AS  ChildField2
INTO    #Children
FROM    OPENJSON(@json, '$')                        AS parent
CROSS APPLY OPENJSON(parent.value, '$.children')    AS child

According to the JSON example, this will be inserted into #Children:

ParentKey ChildKey ChildField1 ChildField2
0 0 21 t2v1
0 1 22 t2v2
1 0 23 t2v3
1 1 24 t2v4

(3) Insert Parent Rows into Table1, and get new Ids:

-----[ Inserting Parents ]-----
DECLARE @ParentKeyToId  AS TABLE (ParentKey INT, ParentId INT)

MERGE   @Table1 USING #Parents AS I
ON 1=0 WHEN NOT MATCHED THEN
INSERT
(
    ParentField1,
    ParentField2
)
VALUES
(
    I.ParentField1,
    I.ParentField2
)
OUTPUT  I.ParentKey, INSERTED.Id INTO @ParentKeyToId;

( ^ This will insert into @ParentKeyToId: Parent JSON Key + New Table1 Id)

(4) Insert Child Rows into Table2

-----[ Inserting Children ]-----
INSERT  @Table2
(
    Table1Id,
    ChildField1,
    ChildField2
)
SELECT  K2I.ParentId,
        I.ChildField1,
        I.ChildField2
FROM    #Children       AS  I
JOIN    @ParentKeyToId  AS  K2I ON  K2I.ParentKey = I.ParentKey

-----[ Removing Temporary Tables ]-----
DROP TABLE #Children
DROP TABLE #Parents

Upvotes: 1

Rohit Garg
Rohit Garg

Reputation: 51

You can use Output clause for this purpose. below is sample code.

create table table1 (id int identity(1,1), col1 varchar(10))
create table #TempTable(id int, col1 varchar(10))
insert into table1
output  inserted.id,inserted.col1 into #TempTable
select 'Sample1'
union select 'sample2'

select * from #TempTable

Upvotes: 0

Related Questions