How to insert multiple JSON rows in SQL

PowerShell Help
1

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

2

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