Powershell foreach INSERT INTO SQL Server DB on multiple rows

Question

I am trying to insert 2 separate arrays into multiple records on 1 SQL Insert Command. I have done the foreach command but it will only accept 1 of the arrays. I have tried doing nested foreach statements but that just puts in way to many records. Below is the code I have so far. I am not posting my connection code to my DB but I ensure you that it connecting to the DB.

$array1 = @(1,2,3)
$array2 = @(a,b,c)

foreach ($file in $array1)
        { 
         $SqlQuery.Append("USE $SQLDBName;")
         $SqlQuery.Append("INSERT INTO tbl_File(Column1, Column2, Column3)")
         $SqlQuery.Append("VALUES('Dummy Data', '$file', '$array2');")
        }

What I am most confused about is how to make both arrays parse correctly into the DB. I hope I explained that correctly. Anything helps!

Here is an example of what it will need to look like:

Column 1   |  Column 2   |  Column 3
Dummy Data   User Input1   User Input1
Dummy Data   User Input2   User Input2
Dummy Data   User Input3   User Input3

This is what I want it to look like with Column 2 being the first array and column 3 being the second array. Column 1 will always be the same.

You want to insert "1, 2, 3, a, b, c" values into Column2 and the full array into Column3? — Iztoksson
– Iztoksson, Commented Dec 6, 2018 at 14:26
Im sorry if I didn't explain it well. I want the first array ($array1) to be in column 2 and the second array ($array2) to be in column 3. — Kris
– Kris, Commented Dec 6, 2018 at 14:31
Can you give us an example of the data you would expect to be in the table if the code worked correctly? — SteveB
– SteveB, Commented Dec 6, 2018 at 14:43
Steve, I added it in accordingly. Let me know if that helps. — Kris
– Kris, Commented Dec 6, 2018 at 14:49

Iztoksson · Accepted Answer · 2019-12-13 08:03:01Z

1

Based on the newly added expected result

$array1 = @(1, 2, 3)
$array2 = @("a", "b", "c")

$sqlQuery = [System.Text.StringBuilder]::new()
$sqlQuery.AppendLine("INSERT INTO tbl_File(Column1, Column2, Column3)")
$sqlQuery.AppendLine("VALUES ")

$hash = @{
    A1 = $array1
    A2 = $array2
}

$counter = $array1.count # Supposedly both arrays always contain same number of elements.
for ($i = 0; $i -le $counter - 1; $i++)
{
    $sqlQuery.AppendLine("('Dummy Data', '" + $hash['A1'][$i] + "', '" + $hash['A2'][$i] + "')")
}

$sqlQuery.ToString();

Result is:

INSERT INTO tbl_File(Column1, Column2, Column3)
VALUES
('Dummy Data', '1', 'a'),
('Dummy Data', '2', 'b'),
('Dummy Data', '3', 'c')

(Old solution) Based on your comments I think this is the result you want in your table:

Column1      Column2      Column3
Dummy Data   1 2 3        a b c

This PS script generates the INSERT statement you need:

$array1 = @(1, 2, 3)
$array2 = @("a", "b", "c")

$sqlQuery = [System.Text.StringBuilder]::new()
$sqlQuery.AppendLine("INSERT INTO tbl_File(Column1, Column2, Column3)")
$sqlQuery.AppendLine("VALUES ")
$sqlQuery.AppendLine("('Dummy Data', '" + "$array1" + "', '" + "$array2" + "')")
$sqlQuery.ToString();

Result is:

INSERT INTO tbl_File(Column1, Column2, Column3)
VALUES
('Dummy Data', '1 2 3', 'a b c')

edited Dec 13, 2019 at 8:03

answered Dec 6, 2018 at 14:45

Iztoksson

9911 gold badge17 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kris Over a year ago

Maybe I am just terrible at explaining. I need each value in my array to be inserted into multiple rows in the DB. Say there are 5 values within each array, I need the first value of both arrays to be inputted into the same row, then the second value to be on a separate row and so on. I do appreciate this though!

Iztoksson Over a year ago

@Kris yes, then the answer provided by Daniel N is correct - the first part, the second is same as mine. You still need to construct the SQL statement though.

Kris Over a year ago

Iztoksson, This worked perfectly, the only issue is that it is telling me my "Dummy data" is the incorrect syntax.

Iztoksson Over a year ago

I'm on my mobile but I see that I'm missing a comma after each line with data, can you try and add it?

Daniel N · Accepted Answer · 2018-12-06 14:46:00Z

1

revised based on your comments. should be easy to put into a sql stmt

This the way to pull values from the two arrays side by side for each index position

$array1 = @(1,2,3)
$array2 = @('a','b','c')

$counter = 0;
foreach ($file in $array1)
        { 
            Write-Host $file $array2[$counter]
            $counter +=1;
        }

if you want an entire array stored in a column, you would need to convert possibly to string an delimit it

$array1 = @(1,2,3)
$array2 = @('a','b','c')

$counter = 0;
foreach ($file in $array1)
        { 
            Write-Host $file ([string]::Join(',', $array2))
            $counter +=1;
        }

edited Dec 6, 2018 at 14:46

answered Dec 6, 2018 at 14:27

Daniel N

1,2181 gold badge9 silver badges14 bronze badges

4 Comments

Kris Over a year ago

Maybe I should have added this in, the values within the array will be changing based on user input. Sometimes there will be 2 values, sometimes there could be 99 values. But one thing is for sure, both arrays will always be the same exact amount of values.

Daniel N Over a year ago

you want an entire array stored in a single row. one column for each array?

Kris Over a year ago

Yes, that is correct. I am not too experienced with all of this, let alone powershell so it is a little confusing but I love the brain work!

Kris Over a year ago

I want the first value of each array stored into the same row of the DB, then the second value of each array stored in the next row and so on. Maybe I am terrible at explaining what I am looking to do. I apologize! I do appreciate your help so far!

Collectives™ on Stack Overflow

Powershell foreach INSERT INTO SQL Server DB on multiple rows

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related