Powershell / PowerCLI - Need more efficient method of inserting dynamic information into SQL

Question

I've used stackoverflow for years, but today is the first time I've felt the need to ask a question. I would like to know if the following code could be made more efficient, this is because I'm writing multiple copies of the script, entering into different SQL tables.

What it does:

• Querys VMware, outputs to $Data
• Truncates SQL table
• Inserts $Data into dbo.T_VM_GUEST_DETAILS
• Inserts everything from dbo.T_VM_GUEST_DETAILS into dbo.T_VM_GUEST_DETAILS_HISTORY

Ideas for efficiency:

Just above ForEach ($Line in $Data), is it possible to include something like "foreach column in $data" which would then dynamically build a 'standard' SQL INSERT query, based on the count of columns, their names & values.

Or is there an easier way to insert $Data into a SQL table elsewhere and I'm making it extra difficult for myself? I am keeping my SQL table column names to the same as the Powershell Output which should help?

The following block fills $Data with a list of VMs (PowerCLI):

        $Data = @()                     
        $AllVMs = Get-VM | SELECT *                     
        $Entry = @()                        
        Foreach ($VM in $AllVMs) {                      
            $Entry                      = "" | Select Name,PowerState,Version,Notes,NumCpu,MemoryGB,VMHost,UsedspaceGB,ProvisionedSpaceGB,GuestId
            $Entry.Name                 = $VM.Name  
            $Entry.PowerState           = $VM.PowerState        
            $Entry.Version              = $VM.Version   
            $Entry.Notes                = $VM.Notes 
            $Entry.NumCpu               = $VM.NumCpu    
            $Entry.MemoryGB             = $VM.MemoryGB      
            $Entry.VMHost               = $VM.VMHost    
            $Entry.UsedspaceGB          = $VM.UsedspaceGB       
            $Entry.ProvisionedSpaceGB   = $VM.ProvisionedSpaceGB                
            $Entry.GuestId              = $VM.GuestId   
            $Data += $Entry                 
        }                       

This then populates $SQL with many SQL commands (could be hundreds or thousands of lines):

$SQL = $NULL                            
$SQL = $SQL + "
    TRUNCATE TABLE dbo.T_VM_GUEST_DETAILS                       
"

ForEach ($Line in $Data) {
    $Name               =   $NULL
    $PowerState         =   $NULL
    $Version            =   $NULL
    $Notes              =   $NULL
    $NumCpu             =   $NULL
    $MemoryGB           =   $NULL
    $VMHost             =   $NULL
    $UsedspaceGB        =   $NULL
    $ProvisionedSpaceGB =   $NULL
    $GuestId            =   $NULL

    $Name               =   $Line.Name.tostring()
    $PowerState         =   $Line.PowerState.tostring()
    $Version            =   $Line.Version.tostring()
    $Notes              =   $Line.Notes.tostring()
    $NumCpu             =   $Line.NumCpu.tostring()
    $MemoryGB           =   $Line.MemoryGB.tostring()
    $VMHost             =   $Line.VMHost.tostring()
    $UsedspaceGB        =   $Line.UsedspaceGB.tostring()
    $ProvisionedSpaceGB =   $Line.ProvisionedSpaceGB.tostring()
    $GuestId            =   $Line.GuestId.tostring()

    $SQL = $SQL + "                         
        INSERT INTO dbo.T_VM_GUEST_DETAILS (                    
            Name,PowerState,Version,Notes,NumCPU,MemoryGB,VMHost,UsedspaceGB,ProvisionedSpaceGB,GuestId,DATE_TIME               
        )                       
        VALUES (                    
            '$Name','$PowerState','$Version','$Notes','$NumCPU','$MemoryGB','$VMHost','$UsedspaceGB','$ProvisionedSpaceGB','$GuestId',GETDATE()             
        )                       
    "
}

$SQL = $SQL + "                     
    INSERT INTO dbo.T_VM_GUEST_DETAILS_HISTORY (                        
        Name,PowerState,Version,Notes,NumCPU,MemoryGB,VMHost,UsedspaceGB,ProvisionedSpaceGB,GuestId,DATE_TIME                   
    )                           
    SELECT Name,PowerState,Version,Notes,NumCPU,MemoryGB,VMHost,UsedspaceGB,ProvisionedSpaceGB,GuestId,DATE_TIME FROM dbo.T_VM_GUEST_DETAILS                        
"

Answer 1

There are a few things you could do here to optimize both efficiency (execution time) and conciseness.

First of all, unless Get-VM returns exactly the properties you need, nothing more and nothing less, you will have to specify the column names - but you can limit yourself to just doing it once, and then reuse that list.

$ColumnNames = @(
  'Name',
  'PowerState',
  'Version',
  'Notes',
  'NumCpu',
  'MemoryGB',
  'VMHost',
  'UsedspaceGB',
  'ProvisionedSpaceGB',
  'GuestId'
)

Starting with the obvious, the first snippet with the foreach($VM in $AllVMs) loop can be entirely replaced with a single pipeline:

$Data = Get-VM |Select-Object -Property $ColumnNames

Not only is it a lot less code to write (and read), you'll also find that it's faster than your own approach.

---

Next up, the SQL statement itself. Instead of inserting just one row at a time, you can insert multiple rows per INSERT statement, like so:

INSERT INTO dbo.T_MY_TABLE (
    Id,Name,Company
) VALUES (1, "John", "MegaCorp Inc."), (2, "Joe", "SohoBiz ltd.")

Depending on the total number of rows, you'll find that this significantly speeds up the insertion on the database side.

There is a limit though, you can insert a maximum of 1000 value rows per INSERT statement, so first we need to partition your $Data array into an array of arrays with a max size of 1000:

$MaxRows = 1000
$DataMatrix = for($i = 0; $i -lt $Data.Length; $i += $MaxRows){
    ,($Data[$i..$($i + $MaxRows - 1)])
}

Now we just need to generate one INSERT statement per array in the $DataMatrix array:

$InsertStatements = foreach($DataSet in $DataMatrix)
{
    # Let's start by generating each VALUES row
    $InsertValues = foreach($Row in $DataSet)
    {
@"
(
        {0},GETDATE()
    )
"@ -f $(@($ColumnNames|ForEach-Object{$Row."$_".ToString()}) -join ',')
# Iterate over $ColumnNames and extract the value of each "column"
    }

    # Now we have all the values we want to insert, now we need the insert statement itself:
@"
INSERT INTO dbo.T_VM_GUEST_DETAILS (                    
        {0},DATE_TIME
    ) VALUES {1};

"@ -f $($ColumnNames -join ','),$($InsertValues -join ', ')
}

Now that we have all of our INSERT statements prepared, all we need is to add the TRUNCATE statement at the top and we're ready to go!

$SQLStatement = @"
TRUNCATE TABLE dbo.T_VM_GUEST_DETAILS

{0}
"@ -f $($InsertStatements -join "`r`n`r`n")

I'll leave the job of adding the INSERT statement for the HISTORY table as an exercise for OP :)

(I apologize for the awkward looking here-strings, but it'll produce nice-looking SQL)