How to insert array and nested array into two different tables (sql server)

Question

This is my Procedure data

DECLARE @customerJson NVARCHAR(MAX) = '[  
        {  
            "customerName": "umar",  
            "contactNo": 252454252,  
            "email": "umar12@gmail.com",  
            "address": "123 Main Street, Suite 100",  
            "region": "North",  
            "countryId": 18,  
            "cityId": 19,  
            "groupId": 1,  
            "plantIds": [  
                1,  
                6,  
                7,  
                8,  
                12  
            ],  
            "customerCode": 12345  
        },  
        {  
            "customerName": "Wilson Farm Supplies",  
            "contactNo": 52452452,  
            "email": "wilsonfarmsupplies@example.com",  
            "address": "456 High Street",  
            "region": "South",  
            "countryId": 194,  
            "cityId": 7,  
            "groupId": 1,  
            "plantIds": [  
                1,  
                6  
            ],  
            "customerCode": 67890  
        }  
    ]'

I want to insert all columns except plantIds into customers table and plantIds into customer_plants table along with the recently inserted customerid which we usually get from scope_identity() function.

What code did you try for this? can you share the code and explain the issue you are facing? — Chetan, Feb 27 '23 at 07:40
I think my blog post entitled [Insert multiple parents with multiple children in a single stored procedure](https://zoharpeled.wordpress.com/2020/05/17/insert-multiple-parents-with-multiple-children-in-a-single-stored-procedure/) will answer your question perfectly. It's an improvement of my answer [here](https://stackoverflow.com/a/34832231/3094533) — Zohar Peled, Feb 27 '23 at 09:48

score 0 · Answer 1 · answered Feb 27 '23 at 08:17

I am first creating your table in order to perform test:

DROP TABLE IF EXISTS customer;

CREATE TABLE customer
(
     [customer_id] INT IDENTITY(1,1) PRIMARY KEY 
    ,[customerName] NVARCHAR(128)
    ,[contactNo] NVARCHAR(128)
    ,[email] NVARCHAR(128)
    ,[address] NVARCHAR(256)
    ,[region] NVARCHAR(32)
    ,[countryId] INT
    ,[cityId] INT
    ,[groupId] INT
);

DROP TABLE IF EXISTS customer_plants;

CREATE TABLE customer_plants 
(
     [customer_id] INT
    ,[plant_id] INT
);

Then, in our code we need additional temporary table which we are going to populate with our JSON data:

DECLARE @customerJson NVARCHAR(MAX) = '[  
        {  
            "customerName": "umar",  
            "contactNo": 252454252,  
            "email": "umar12@gmail.com",  
            "address": "123 Main Street, Suite 100",  
            "region": "North",  
            "countryId": 18,  
            "cityId": 19,  
            "groupId": 1,  
            "plantIds": [  
                1,  
                6,  
                7,  
                8,  
                12  
            ],  
            "customerCode": 12345  
        },  
        {  
            "customerName": "Wilson Farm Supplies",  
            "contactNo": 52452452,  
            "email": "wilsonfarmsupplies@example.com",  
            "address": "456 High Street",  
            "region": "South",  
            "countryId": 194,  
            "cityId": 7,  
            "groupId": 1,  
            "plantIds": [  
                1,  
                6  
            ],  
            "customerCode": 67890  
        }  
    ]';

DROP TABLE IF EXISTS #customer_plants;

CREATE TABLE #customer_plants 
(
     [customer_id] INT 
    ,[customerName] NVARCHAR(128)
    ,[contactNo] NVARCHAR(128)
    ,[email] NVARCHAR(128)
    ,[address] NVARCHAR(256)
    ,[region] NVARCHAR(32)
    ,[countryId] INT
    ,[cityId] INT
    ,[groupId] INT
    ,[plantIds] NVARCHAR(MAX)
);

INSERT INTO #customer_plants ([customerName], [contactNo], [email], [address], [region], [countryId], [cityId], [groupId], [plantIds])
SELECT *
FROM OpenJson(@customerJson)
WITH 
(   
     [customerName] NVARCHAR(128)  '$.customerName'
    ,[contactNo] NVARCHAR(128)  '$.contactNo'
    ,[email] NVARCHAR(128)  '$.email'
    ,[address] NVARCHAR(256)  '$.address'
    ,[region] NVARCHAR(32)  '$.region'
    ,[countryId] INT  '$.countryId'
    ,[cityId] INT  '$.cityId'
    ,[groupId] INT  '$.groupId'
    ,[plantIds] NVARCHAR(MAX)  AS JSON  
 );

The next step is to insert the data into the customer table. Then, we are extracting back the new customer ids depending on the email (I am assuming the email is unique, if you many add more columns in the join statement).

INSERT INTO customer ([customerName], [contactNo], [email], [address], [region], [countryId], [cityId], [groupId])
SELECT [customerName], [contactNo], [email], [address], [region], [countryId], [cityId], [groupId]
FROM #customer_plants

UPDATE #customer_plants
SET [customer_id] = T.[customer_id]
FROM #customer_plants S
INNER JOIN customer T
    ON S.[email] = T.[email]

INSERT INTO customer_plants
SELECT C.customer_id
      ,p.value
FROM #customer_plants C
CROSS APPLY OpenJson([plantIds]) P;

Finally, check the data:

SELECT *
FROM customer;

SELECT *
FROM customer_plants;

How to insert array and nested array into two different tables (sql server)

1 Answers1