none
ForEach Statement only returns one value RRS feed

  • Question

  • Hello,

    I've written this PS script to return multiple values from a 'Get' command and place those values into an INSERT statement.  However, it will only return one record instead of multiple.

    $billing = Get-AzConsumptionUsageDetail -BillingPeriodName 202001 -InstanceName STAGING -Top 10
    
    foreach($bill in $billing)
        {
            $usageStart = $bill.UsageStart
            $usageEnd = $bill.UsageEnd
            $billingPeriod = $bill.BillingPeriodName
            $instanceName = $bill.InstanceName
            $usageQty = $bill.UsageQuantity
            $billQty = $bill.BillableQuantity
            $preTax = $bill.PretaxCost
            $meterID = $bill.MeterId    
    
            $billingQRY = "
                            INSERT INTO [dbo].[AzureBilling]
                                            (
                                                [UsageStart],
                                                [UsageEnd],
                                                [BillingPeriodName],
                                                [InstanceName],
                                                [UsageQuantity],
                                                [BillableQuantity],
                                                [PretaxCost],
                                                [MeterID])
                                            VALUES
                                            (
                                                '$usageStart',
                                                '$usageEnd',
                                                '$billingPeriod',
                                                '$instanceName',
                                                '$usageQty',
                                                '$billQty',
                                                '$preTax',
                                                '$meterID'
                                            )
                            GO
                        "
        }
    I can't figure out what I'm doing wrong.

    Thanks,

    Frank

    Tuesday, February 11, 2020 5:52 PM

All replies

  • Wihtout having any clue about databases ... you are creating your INSERT statement inside your variable $billingQRY in your foreach loop again and again and again but you never output it in any way. The simplest would be to output it to the console to make it visible by calling the variable inside your loop after creating it.

    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''

    Tuesday, February 11, 2020 10:11 PM
  • This is how to use PowerShell to generate output from a loop:

    $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 10 |
        ForEach-Object{
            $sql -f $_.UsageStart,$_.UsageEnd,$_.BillingPeriodName,$_.InstanceName,$_.UsageQuantity,$_.BillableQuantity,$_.PretaxCost,$_.MeterId
        }
    

    It would be faster to use a parameterized query to insert multiple values into a table.


    \_(ツ)_/

    Tuesday, February 11, 2020 11:13 PM
  • It worked to a degree.

    The array returns two insert statements for each record like the following:

    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}'
    )

    Also, when I run the 'Invoke-Sqlcmd' cmdlet, it inserts the index numbers, i.e.) {0}, {1}, etc, instead of the values.

    • Edited by anelliaf Wednesday, February 12, 2020 3:01 PM
    Wednesday, February 12, 2020 2:28 PM
  • You obviously copied the code inco4rrectly or you changed it.  What I posted cannot do that.

    \_(ツ)_/

    Wednesday, February 12, 2020 5:36 PM