How to insert multiple JSON rows in SQL

Tagged: 

This topic contains 1 reply, has 1 voice, and was last updated by  Martynas 4 months, 1 week ago.

  • Author
    Posts
  • #68506

    Martynas
    Participant

    Hey,
    I have insert statement:

    $SQLinsert = "INSERT INTO $Table (UserID, ID, Title, Body) 
       VALUES ('$($result.userId)','$($result.id)', '$($result.title)', '$($result.completed)');
    

    But it is importing only one record. Now I have $result as an array and want to insert all the values with one INSERT statement.

    SQL should look like this: INSERT INTO Table (UserID, ID, Title, Body) VALUES (1, 2, Test1, Test2), (2, 3, Test3, Test4)

    But cant think of how to format the INSERT sql statement with multiple rows.

    Thank you,
    Marty

  • #68515

    Martynas
    Participant

    OK, so I managed to insert multiple rows using -f parameter (Don's example from back 2011).

    And this is how it looks now:

    foreach ($row in $result)
    {
      $cmd.commandtext = "INSERT INTO $Table (UserID, ID, Title, Body) VALUES ( '{0}', '{1}','{2}','{3}')" -f $row.userID, $row.id, $row.title, $row.completed
      $cmd.ExecuteNonQuery()
    }
    

    The way how it executes is:

    INSERT INTO test2 (UserID, ID, Title, Body) VALUES ( '1', '2','quis ut nam facilis et officia qui','False')
    INSERT INTO test2 (UserID, ID, Title, Body) VALUES ( '1', '3','fugiat veniam minus','False')
    INSERT INTO test2 (UserID, ID, Title, Body) VALUES ( '1', '4','et porro tempora','True')
    

    The question is from efficiency perspective, is it the right way to insert multiple rows?

    As I am thinking from my original post that going with one INSERT statement and multiple insert values should work faster.

    Any feedback is welcome.

    Thanks,
    Marty

You must be logged in to reply to this topic.