PowerShell SQL Query to Update Table

Question

I am using PowerShell to run a SQL query. I then want to update another table based on information pulled from the query. I have tested my SQL query and update statements directly in the SQL Server Management Studio so I know that they work. The results of those tests show that there are over 800 records that should be updated. However, when I run the same query and update from within PowerShell, it only updates one record. I have mostly copied this script from another much larger script that was written in a similar format. But it appears that I am missing a ForEach loop (or something similar) but cannot figure out where to place it or how. Here is my script:

# Set the database connection strings
    $Conn02 = new-object System.Data.SqlClient.SqlConnection "SERVER_INFORMATION"
    $mySQL02 = New-Object System.Data.SqlClient.SqlCommand
    $mySQL02.Connection = $Conn02

    $Conn03 = new-object System.Data.SqlClient.SqlConnection "SERVER_INFORMATION"
    $mySQL03 = New-Object System.Data.SqlClient.SqlCommand
    $mySQL03.Connection = $Conn03
    

#Connect to the database to perform the query
$Conn02.Open()

$mySQL02.CommandText = "SELECT IDNUM, FNAME, LNAME
        FROM TABLE1
        WHERE STATUS = 'C'"
        
 $SQL02 = $mySQL02.ExecuteReader()
 
       WHILE($SQL02.Read()){

        $NEWID = $SQL02['ID_NUM']
        $FNAME1 = $SQL02['FNAME']
        $LNAME1 = $SQL02['LNAME']
    }

#Run the update
$Conn03.Open()

    $mySQL03.CommandText = 
    
    "INSERT INTO TABLE2 (user_id,firstname,lastname)
     VALUES ('$NEWIDme','$FNAME1','$LNAME1')"

Thank you for your time

You perform only a single INSERT, so obviously you will only ever end up inserting a single row. The loop that reads the result just ends up discarding everything but the very last row. You should move the INSERT into the read loop (but still use a separate connection, since the reader is occupying the other one). Make sure to call .Close() on your connections when all is said and done (readers and commands technically need to be disposed as well, but closing the connection takes care of that). — Jeroen Mostert
– Jeroen Mostert, Commented Feb 14, 2022 at 21:53
Also, issuing commands through string interpolation opens you up to SQL injection, or at the very least errors when strings need escaping (last name O'Toole will not work correctly, for example, nor will NULL values). Use properly parameterized statements; it's a bit more work but it pays off. A snippet for your perusal. — Jeroen Mostert
– Jeroen Mostert, Commented Feb 14, 2022 at 21:56

Hussein · Accepted Answer · 2022-02-15 00:35:23Z

As @jeroen said, you need to move the Insert statement into the while loop. Here is the code should look like:



#Connect to the database to perform the query
$Conn02.Open()

$mySQL02.CommandText = "SELECT IDNUM, FNAME, LNAME
        FROM TABLE1
        WHERE STATUS = 'C'"
        
 $SQL02 = $mySQL02.ExecuteReader()

 #Save reader into datatable
 $Datatable = New-Object System.Data.DataTable
 $Datatable.Load($SQL02) 

 #Close the connection
 $Conn02.Close()


 #Run the update
  $Conn03.Open()
  foreach($row in $dt){

        $NEWID = $row['ID_NUM']
        $FNAME1 = $row['FNAME']
        $LNAME1 = $row['LNAME']
  
        $mySQL03.CommandText =     
        "INSERT INTO TABLE2 (user_id,firstname,lastname)
        VALUES ('$NEWIDme','$FNAME1','$LNAME1')"

        $mySQL03.ExecuteNonQuery() 

    }
  $Conn03.Close()

To prevent SQL Injection I suggest using Parameters when assigning values

 foreach($row in $dt){

        $NEWID = $row['ID_NUM']
        $FNAME1 = $row['FNAME']
        $LNAME1 = $row['LNAME']
  
        $mySQL03.CommandText =     
        "INSERT INTO TABLE2 (user_id,firstname,lastname)
        VALUES (@NEWIDme,@FNAME1,@LNAME1)"
        
        $mySQL03.Parameters.Clear()
        $Command.Parameters.AddWithValue('@NEWIDme',$NEWID)  | Out-Null
        $Command.Parameters.AddWithValue('@FNAME1',$FNAME1)  | Out-Null
        $Command.Parameters.AddWithValue('@LNAME1',$LNAME1)  | Out-Null 
        $mySQL03.ExecuteNonQuery() 

    }

Collectives™ on Stack Overflow

PowerShell SQL Query to Update Table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related