Invoke-SqlCmd to -OutputAs Datatables - then edit the datatable before writing back to SQL db

Question

The following works fine - now I would like to change a date column in the DataTables output before writing back to the sql database:

Invoke-Sqlcmd -ServerInstance $instance -Database master -InputFile $scriptPath$script  -OutputAs DataTables |
            Write-SqlTableData -ServerInstance $collectionInstance -DatabaseName $collectionDatabase -SchemaName "dbo" -TableName $script.Replace(".sql","") -Force

...OutputAs DataTables | ForEach-Object {$_.CaptureDate = @( "'$($capturedate)'")} |

So add the For-Each to change the datatable output.

score 0 · Answer 1 · answered Oct 28 '21 at 07:28

    $dt = New-Object System.Data.DataTable
    $dt= (Invoke-Sqlcmd -ServerInstance $instance -Database master -InputFile $scriptPath$script  -OutputAs DataTables) 
    foreach($row in $dt)
    { 
        $row.CaptureDate=[DateTime]$capturedate
    }
    Write-SqlTableData -ServerInstance $collectionInstance -DatabaseName $collectionDatabase -SchemaName "dbo" -TableName $script.Replace(".sql","_tmp") -InputData $dt -Force`

This is my workaround - not sure if its affecting speed but it would be nice to have it flow seemlessly after the Invoke-Sqlcmd and before the Write-SqlTableData

Invoke-SqlCmd to -OutputAs Datatables - then edit the datatable before writing back to SQL db

1 Answers1