CODEWITHSUNDEEP
sqlsql-serverpowershellforeach
Kris
Kris

Reputation: 23

Powershell foreach INSERT INTO SQL Server DB on multiple rows

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.

Upvotes: 1

Views: 4641

Answers (2)

Iztoksson
Iztoksson

Reputation: 990

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')

Upvotes: 1

Daniel N
Daniel N

Reputation: 1208

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;
        }

Upvotes: 1

Related Questions