Update table in SQL Server using DBATOOLS

Question

I have this code that update several column in my table with value from an excel sheet. I tried using sqlserver module to achieve this but i am wondering is there any way to use DBATOOLS module instead to perform the operation?

for ($row8 = 9; $row8 -lt 40; $row8++) {
    if ([string]::IsNullOrEmpty($ws3.Cells.Item($row8, 3).Value2)){break} else {
        $sqlCmd.CommandText = "UPDATE $Table7 SET STCW_Cert_Held = $ws3.Cells.Item($row8, 18).Value2, 
        ISPS = $ws3.Cells.Item($row8, 19).Value2,
            Marine_Medical_Exp = $ws3.Cells.Item($row8, 20).Value2,
            Petronas_Medical_Exp = $ws3.Cells.Item($row8, 21).Value2,
        OSP_Exp = $ws3.Cells.Item($row8, 22).Value2,
            Shell_SafetyPassport_Exp = $ws3.Cells.Item($row8, 23).Value2,
            Boseit_Exp = $ws3.Cells.Item($row8, 24).Value2,
        BT_STCW_Exp = $ws3.Cells.Item($row8, 25).Value2,
            Rigging_Slinging_Exp = $ws3.Cells.Item($row8, 26).Value2,
            FoodHandling_Exp = $ws3.Cells.Item($row8, 27).Value2,
        H2S_Exp = $ws3.Cells.Item($row8, 28).Value2,
        COC_Exp = $ws3.Cells.Item($row8, 29).Value2,
            COR_Exp = $ws3.Cells.Item($row8, 30).Value2,
            Seaman_Card_Exp = [string] $ws3.Cells.Item($row8, 31).Value2,
        Passport_Exp = [string] $ws3.Cells.Item($row8, 32).Value2 WHERE PasportNumber =  $ws3.Cells.Item($row8, 3).Value2"

    }
}

Please read how to [ask] and stop [deleting](https://stackoverflow.com/questions/64748790/dbatools-update-sql-table) previous [questions](https://stackoverflow.com/questions/64745385/dbatools-how-to-bulk-update-sql-table-data) about your problem. — vonPryz, Nov 10 '20 at 09:24

vonPryz · Accepted Answer · 2020-11-12T07:34:40.470

Updating a table can be done with Invoke-DbaQuery. Instead of catenating data from Excel into a single statement, use parametrized queries. That is, create a collection of varialbes and pass those as query parameter. See docs' example 5. This makes code much more simple to read and protects you for SQL injections too. Like so,

$query = 'UPDATE $Table7 SET STCW_Cert_Held = @STCW_Cert_Held, ISPS = @ISPS, ...  where PasportNumber = @PasportNumber'

for ($row8 = 9; $row8 -lt 40; $row8++) {
    if ([string]::IsNullOrEmpty($ws3.Cells.Item($row8, 3).Value2)){break} else {

        # Build the parameter set
        $params = @{
            STCW_Cert_Held = $ws3.Cells.Item($row8, 18).Value2  # Description
            ISPS = $ws3.Cells.Item($row8, 19).Value2    
            # ... omitted lots of parameters
            PasportNumber =  $ws3.Cells.Item($row8, 3).Value2   # Other notes
        }

        Invoke-DbaQuery -SqlInstance . -Query $query -SqlParameters $params
    }
}

While you are at it, please change the variable names and avoid magic numbers. As of now, there's $row8 - what does that even mean? You start reading from 8th row, right? When the Excel source changes - they always do - $row8 doesn't make any sense. Also, where do you get the upper limit of 40? You should have a constant with clear name. Like so,

# Skip 7 rows of headers
$startRow = 8 
# Explain why there will not be more than 40 rows
$lastRow = 40 
# Even better, use a function that finds out how 
$lastRow = Get-LastRow($myExcelFile) much to import.

for ($row = $startRow; $ro8 -lt $lastRow; $row++) {
    ...
}

When I execute the code, produce this error "A null key is not allowed in a hash literal." although the cell in the excel file has value. Why that this error happen? — sab, Nov 12 '20 at 03:04
@sab That's great. Tick the answer mark, so other people who read the question see that there's a working answer. — vonPryz, Nov 12 '20 at 11:10

Update table in SQL Server using DBATOOLS

1 Answers1