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

i've used stackoverflow years, today first time i've felt need ask question. know if following code made more efficient, because i'm writing multiple copies of script, entering different sql tables.

what does:

• querys vmware, outputs $data
• truncates sql table
• inserts $data dbo.t_vm_guest_details
• inserts dbo.t_vm_guest_details dbo.t_vm_guest_details_history

ideas efficiency:

just above foreach ($line in $data), possible include "foreach column in $data" dynamically build 'standard' sql insert query, based on count of columns, names & values.

or there easier way insert $data sql table elsewhere , i'm making difficult myself? keeping sql table column names same powershell output should help?

the following block fills $data 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 populates $sql many sql commands (could 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 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 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 dbo.t_vm_guest_details                         " 

there few things here optimize both efficiency (execution time) , conciseness.

first of all, unless get-vm returns exactly properties need, nothing more , nothing less, will have specify column names - can limit doing once, , reuse list.

$columnnames = @(   'name',   'powerstate',   'version',   'notes',   'numcpu',   'memorygb',   'vmhost',   'usedspacegb',   'provisionedspacegb',   'guestid' ) 

starting obvious, first snippet foreach($vm in $allvms) loop can entirely replaced single pipeline:

$data = get-vm |select-object -property $columnnames 

not lot less code write (and read), you'll find it's faster own approach.

---

next up, sql statement itself. instead of inserting 1 row @ time, can insert multiple rows per insert statement, so:

insert dbo.t_my_table (     id,name,company ) values (1, "john", "megacorp inc."), (2, "joe", "sohobiz ltd.") 

depending on total number of rows, you'll find speeds insertion on database side.

there limit though, can insert a maximum of 1000 value rows per insert statement, first need partition $data array array of arrays max size of 1000:

$maxrows = 1000 $datamatrix = for($i = 0; $i -lt $data.length; $i += $maxrows){     ,($data[$i..$($i + $maxrows - 1)]) } 

now need generate 1 insert statement per array in $datamatrix array:

$insertstatements = foreach($dataset in $datamatrix) {     # let's start generating each values row     $insertvalues = foreach($row in $dataset)     { @" (         {0},getdate()     ) "@ -f $(@($columnnames|foreach-object{$row."$_".tostring()}) -join ',') # iterate on $columnnames , extract value of each "column"     }      # have values want insert, need insert statement itself: @" insert dbo.t_vm_guest_details (                             {0},date_time     ) values {1};  "@ -f $($columnnames -join ','),$($insertvalues -join ', ') } 

now have of our insert statements prepared, need add truncate statement @ top , we're ready go!

$sqlstatement = @" truncate table dbo.t_vm_guest_details  {0} "@ -f $($insertstatements -join "`r`n`r`n") 

i'll leave job of adding insert statement history table exercise op :)

(i apologize awkward looking here-strings, it'll produce nice-looking sql)