Make Write-SqlTableData AzureAD access token aware

[SQLvariant]

The Invoke-Sqlcmd cmdlet has and extremely useful parameter called -AccessToken which allows to pass in an Access Token you retrieve with the Get-AzAccessToken cmdlet.

The Write-SqlTableData cmdlet accepts a -Credential object, which is great when I'm using an AppID. However, when I'm using a system-Managed Identity I need to use an Access Token, and the command doesn't have that parameter option.

Without the -AccessToken parameter on the Write-SqlTableData cmdlet I am forced to use a foreach loop to RBAR (Row by Agonizing Row) over the rows I have in PowerShell and INSERT them one at a time in using the Invoke-Sqlcmd cmdlet. This approach usually gets the job done, but obviously doesn't perform well for medium - large amounts of data to insert (noticeable slowdowns can be observed with as little as 300 rows).

What makes the situation worse is that the Invoke-Sqlcmd cmdlet can only get the job done when the data is very straight-forward. When the data contains characters like ' and " additional logic has to be added to every column in the VALUES section of the INSERT statement. Whereas the Write-SqlTableData cmdlet just handles this for you without having to add any additional logic.

[Matteo-T]

@SQLvariant this is looking promising..

[Matteo-T]

This is going to be fixed in v22 GA, which I expect to roll out in the next few days (or hours).