0

Here is the example json -

     "Root Node - Type 1": {
                "Attributes": {
                    "Items": [
                        {
                            "A": {
                                "Value": "A1"
                            },
                            "B": {
                                "Value": "B1"
                            },
                            "C": {
                                "Value": "C1"
                            },
                            "D": {
                                "Value": "D1"
                            }
                        },
                        {
                            "A": {
                                "Value": "A2"
                            },
                            "B": {
                                "Value": "B2"
                            },
                            "C": {
                                "Value": "C2"
                            },
                            "D": {
                                "Value": "D2"
                            }
                       }
            ]
    }
}

I need to insert A, B in table 1 and take the identity from table 1 and insert into table 2. Also, I need to insert C, D into table 3. Items here will be a large array. I am looking to bulk insert this data into tables. Any ideas?

Table structure -

 CREATE TABLE [Table 1](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [A] [varchar](100) NULL,
 [B] [varchar](15) NULL,
 [CreatedBy] [varchar](255) NULL,
 [CreatedDate] [datetime] NULL


 CREATE TABLE [Table 2](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Table1_Id] int,
 [CreatedBy] [varchar](255) NULL,
 [CreatedDate] [datetime] NULL

 CREATE TABLE [Table 3](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Table1_Id] [int] NOT NULL,
 [Name] [nvarchar](100) NOT NULL,
 [Value] [nvarchar](1000) NULL

Data 0 enter image description here

Mm77
  • 85
  • 1
  • 12
  • What have you tried so far? DDL of your tables will help us help you. – Thom A Oct 05 '20 at 18:11
  • I've written about [inserting multiple parents and children in the same procedure](https://zoharpeled.wordpress.com/2020/05/17/insert-multiple-parents-with-multiple-children-in-a-single-stored-procedure/) in my blog, but that solution works with table valued parameters, not with Json. If you could [edit] your question with the DDL of the tables and give us a little bit better sample data, someone will probably be able to help you with that. – Zohar Peled Oct 05 '20 at 18:20
  • I am passing the json from azure logic app. I tried to insert this data in logic app. But, its taking a lot of time.So, I am trying to convert that to stored procedure instead. I tried to write a cursor to loop through the items. I want to know whats the most efficient way to insert here. – Mm77 Oct 05 '20 at 18:33
  • DDL, not images, please. – Thom A Oct 05 '20 at 18:44
  • Updated with DDL – Mm77 Oct 05 '20 at 20:17
  • What gets inserted into column A of Table 1? In the JSON there are 2 elements in the Items array which contain a key of "A". Should the insert be 2 rows with the same value ("1") into column A of Table 1? – SteveC Oct 05 '20 at 22:54
  • You've done a good job posting DDL. I've edited your question to make the sample json a bit more clear, now if you could [edit] to show the results of the insert process - then it would be easier to help you. – Zohar Peled Oct 06 '20 at 06:05
  • Look at https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15 since SQL Server 2016 built-in functions exist for JSON data – droebi Oct 06 '20 at 08:02
  • https://stackoverflow.com/questions/46323946/how-to-insert-json-object-to-sql-server-2016-as-nvarchar - i think this helps ;) – droebi Oct 06 '20 at 08:12

0 Answers0