Invoke-Sqlcmd inserting variables from array and not data values

PowerShell Help
1

Hello,

The script below pulls records from Azure billing.

[pre]

$sql = @’
INSERT INTO [dbo].[AzureBilling](
[UsageStart],
[UsageEnd],
[BillingPeriodName],
[InstanceName],
[UsageQuantity],
[BillableQuantity],
[PretaxCost],
[MeterID]
)
VALUES(
‘{0}’,
‘{1}’,
‘{2}’,
‘{3}’,
‘{4}’,
‘{5}’,
‘{6}’,
‘{7}’
)
go
'@

Get-AzConsumptionUsageDetail -BillingPeriodName 202001 -InstanceName STAGING -Top 1 |
ForEach-Object{
$sql -f $.UsageStart,$.UsageEnd,$.BillingPeriodName,$.InstanceName,$.UsageQuantity,$.BillableQuantity,$.PretaxCost,$.MeterId
}

[/pre]

I’m trying to create an insert statement and use the invoke-sqlcmd to load the data. However, it seems to be inserting the index arrays instead of the data.

[pre]

INSERT INTO [dbo].[AzureBilling](
    [UsageStart],
    [UsageEnd],
    [BillingPeriodName],
    [InstanceName],
    [UsageQuantity],
    [BillableQuantity],
    [PretaxCost],
    [MeterID]
)
VALUES(
    '1/1/2020 12:00:00 AM',
    '1/1/2020 11:59:59 PM',
    '20200101',
    'STAGING',
    '22.6451612832258',
    '',
    '2.81829032873185',
    '1450c4f7-f8db-4fc'
)
go
INSERT INTO [dbo].[AzureBilling](
    [UsageStart],
    [UsageEnd],
    [BillingPeriodName],
    [InstanceName],
    [UsageQuantity],
    [BillableQuantity],
    [PretaxCost],
    [MeterID]
)
VALUES(
    '{0}',
    '{1}',
    '{2}',
    '{3}',
    '{4}',
    '{5}',
    '{6}',
    '{7}'
)

[/pre]

I seen there is a ‘-variable’ option, but unsure how I would use it.

Thanks,

Frank

2

In the code you are not showing how you are calling SQL, but you are not saving the commands you created to variable. Additionally, GO cannot be used, it’s only relevant in certain places. Use a semicolon to end each command:

$sql = @'
INSERT INTO [dbo].[AzureBilling](
[UsageStart],
[UsageEnd],
[BillingPeriodName],
[InstanceName],
[UsageQuantity],
[BillableQuantity],
[PretaxCost],
[MeterID]
)
VALUES(
'{0}',
'{1}',
'{2}',
'{3}',
'{4}',
'{5}',
'{6}',
'{7}'
);

'@

$sqlCmd = Get-AzConsumptionUsageDetail -BillingPeriodName 202001 -InstanceName STAGING -Top 1 |
          ForEach-Object{
              $sql -f $_.UsageStart,$_.UsageEnd,$_.BillingPeriodName,$_.InstanceName,$_.UsageQuantity,$_.BillableQuantity,$_.PretaxCost,$_.MeterId
          }

Invoke-SQLCmd -Query $sqlCmd

Also, it’s not a best practice to assume a GET returns something, it would be better to follow logic like this:

$sql = @'
INSERT INTO [dbo].[AzureBilling](
[UsageStart],
[UsageEnd],
[BillingPeriodName],
[InstanceName],
[UsageQuantity],
[BillableQuantity],
[PretaxCost],
[MeterID]
)
VALUES(
'{0}',
'{1}',
'{2}',
'{3}',
'{4}',
'{5}',
'{6}',
'{7}'
);

'@

$usage = Get-AzConsumptionUsageDetail -BillingPeriodName 202001 -InstanceName STAGING -Top 1

if ($usage) {
    $sqlCmd = foreach ($item in $usage) {

        $sql -f $item.UsageStart,
                $item.UsageEnd,
                $item.BillingPeriodName,
                $item.InstanceName,
                $item.UsageQuantity,
                $item.BillableQuantity,
                $item.PretaxCost,
                $item.MeterId
    }

    Invoke-SQLCmd -Query $sqlCmd
}
else {
    'No usage'
}
3

Great points! It works for returning 1 record. I’m receiving an error when I try to return all records:

[pre]

Invoke-Sqlcmd : Cannot convert ‘System.Object’ to the type ‘System.String’ required by parameter ‘Query’. Specified method is not supported.
At line:39 char:88

  • … Instance “CQ\SERVER” -Database sqltest -Query $sqlCmd
  • CategoryInfo : InvalidArgument: (:slight_smile: [Invoke-Sqlcmd], ParameterBindingException
  • FullyQualifiedErrorId : CannotConvertArgument,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

[/pre]

I’m assuming it has something to do with building the array.

4

Yes, it’s most likely a string array, so you can just one of the following:

$content = @"
The color is {0}
"@


$result = foreach ($item in 'red','green','blue') {
    $content -f $item
}

#join with line break
$result -join "`r`n"
#pipe to Out-String
$result | Out-String
5

That worked perfectly!

I ran it for the entire month for one resource and it wrote 91 records into the database! At this point, it seems the easiest route to load the data instead of writing to file and then loading. I may need to change if we start to report outside of our group to make this method more efficient.

Here’s the final code:

[pre]

$sql = @’
INSERT INTO [dbo].[AzureBilling](
[UsageStart],
[UsageEnd],
[BillingPeriodName],
[InstanceName],
[UsageQuantity],
[BillableQuantity],
[PretaxCost],
[MeterID]
)
VALUES(
‘{0}’,
‘{1}’,
‘{2}’,
‘{3}’,
‘{4}’,
‘{5}’,
‘{6}’,
‘{7}’
);
'@

$usage = Get-AzConsumptionUsageDetail -BillingPeriodName 202001 -InstanceName STAGING

if ($usage) {
$sqlCmd = foreach ($item in $usage) {

$sql -f $item.UsageStart,
        $item.UsageEnd,
        $item.BillingPeriodName,
        $item.InstanceName,
        $item.UsageQuantity,
        $item.BillableQuantity,
        $item.PretaxCost,
        $item.MeterId 
}

# string array; join with line break
$sqlCmd -join "`r`n"
# pipe to Out-String
$qry = $sqlCmd | Out-String
Invoke-Sqlcmd -ServerInstance "CQ\SERVER" -Database sqltest -Query $qry

}
else {
‘No usage’
}

[/pre]

Again, thanks for all of your help!

6

If you are strictly inserting records, there are better methods:

Write-SqlTableData

Not messed with it personally, but the idea is you would be able to do:

Get-AzConsumptionUsageDetail ... | Write-SqlTableData ..

Normally, I do bulk inserts as I’m getting 10k=50k records and inserting them in SQL and have done inserts with 100k+ and it takes seconds. Only drawback is you have to convert from PSObject > DataTable > Map Columns > Insert. The Write-SqlDataTable looks like it takes some of the work out of the middle and would fit your needs rather than building a sql script to insert rows manually.

https://www.sqlshack.com/6-methods-write-powershell-output-sql-server-table/

7

Seems much simpler to load data, and possibly quicker. I didn’t time, but the query finished in a few seconds for 91 records.

[pre]

(Get-AzConsumptionUsageDetail -BillingPeriodName 202001 -InstanceName STAGING | Select-Object -Property UsageStart, UsageEnd, BillingPeriodName, InstanceName, UsageQuantity, BillableQuantity, PretaxCost, MeterId) |
Write-SqlTableData -ServerInstance “” -DatabaseName “” -SchemaName “dbo” -TableName “” -Force

[/pre]

Thanks!