Cannot update SQL Server table from powershell

Question

I have created a test database in SQL Server 2016 Express, it holds 1 table labeled drivers.

I use PowerShell to perform ciminstance query of installed drivers, then insert those values into the test database driver table. (the insert works as expected) The issue I have is attempting to update the driver table, only the last object is inserted into the database 40 times(that is how many drivers are returned from the ciminstance query). I have created 2 PowerShell scripts

Insert values
Update values

Stumped!

$database = 'test'
$server = 'groga\sqlExpress'
$table = 'dbo.Driver'
$SQLServer = "groga\sqlExpress"
$SQLDBName = "test"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = 
$SQLDBName; Integrated Security = True"

$SqlConnection.Open()
$today = Get-Date

$drivers = gcim win32_pnpsigneddriver -Property * 
$model = gcim win32_computersystem -Property *
foreach($driver in $drivers)
{
if(!($driver.Description -match "Generic") -and $driver.Manufacturer -
notmatch 'Microsoft|Standard|Generic' -and $driver.DriverDate -ne $null)
{
    $count = New-Object psobject -Property @{

        'Date' = $driver.DriverDate
        'Manufacturer' = $driver.Manufacturer
        'Version' = $driver.DriverVersion
        'PackageID' = "0"
        'SKU' = $model.SystemSKUNumber
        'Model' = $model.Model
        'Today' = $today} 

$col1 = $count.Date
$col2 = $count.Manufacturer
$col3 = $count.Version
$col4 = $count.PackageID
$col5 = $count.SKU
$col6 = $count.Model
$col7 = $count.Today

$update = @"
   UPDATE $table
   SET [Date]='$col1',
     [Manufacturer]='$col2',
     [Version]='$col3',
     [PackageID]='$col4',
     [SKU]='$col5',
     [Model]='$col6',
     [Today]='$col7'     
"@

  $dbwrite = $SqlConnection.CreateCommand()
  $dbwrite.CommandText = $update
  $dbwrite.ExecuteNonQuery() 
  }

}

$Sqlconnection.Close()

Be careful when updating records. If you omit the `WHERE` clause, **all** records will be updated! — JosefZ, Jun 18 '17 at 01:27
Thank you JosefZ, I am actually trying to update all records. — grunzer, Jun 18 '17 at 04:44

score 0 · Answer 1 · answered Jun 19 '17 at 01:21

The UPDATE statement will apply to all rows that are matched by the query. So what your script is doing is setting ALL rows in the table to info for a driver then doing the same for the whole list.

You will need to determine the fields which uniquely identify each driver and then filter your query down to that. Looking at sample driver info, this could be Date, Manufacturer, Device Name (something you would need to add to your schema), DriverVersion.

Example with just Date, Manufacturer, DriverVersion:

$update = @"
   UPDATE $table
   SET [PackageID] = '$col4'
     [SKU]='$col5',
     [Model]='$col6',
     [Today]='$col7'     
     WHERE [Date] = '$col1' AND [Manufacturer]='$col2' AND [Version]='$col3' 
"@

Thank you Alec, I will rewrite the update query and test – grunzer Jun 19 '17 at 06:31 — grunzer, Jun 19 '17 at 06:31

Cannot update SQL Server table from powershell

1 Answers1