title: Use Powershell to insert data into multiple columns in SQL Server
body: <p>I'm trying to insert data collected from VMware vCenter into a database using Powershell and SQL Server.</p>
  
  <p>This is what I have for now:</p>
  
  <pre><code>$conn = New-Object System.Data.SqlClient.SqlConnection
  $conn.ConnectionString = "Data Source=.\sqlexpress;Initial Catalog=Computer;Integrated Security=SSPI;"
  $conn.open()
  $cmd = New-Object System.Data.SqlClient.SqlCommand
  $cmd.connection = $conn
  
  $val = 0
  $clusterinf = foreach($cluster in Get-Cluster){
  $esx = $cluster | Get-VMHost
  $ds = Get-Datastore -VMHost $esx | where {$_.Type -eq "VMFS"}
  
  $cluster | Select @{N="VCname";E={$cluster.Uid.Split(':@')[1]}},
      @{N="DCname";E={(Get-Datacenter -Cluster $cluster).Name}},
      @{N="Clustername";E={$cluster.Name}},        
      @{N="TotalPhysicalMemory";E={[Math]::Round(($esx | Measure-Object -Property MemoryTotalMB -Sum).Sum /1KB)}},
      @{N="TotalUsedMemory";E={[Math]::Round(($esx | Measure-Object -Property MemoryUsageMB -Sum).Sum /1KB)}},
      @{N="AvailableMemroy";E={[Math]::Round(($esx | Measure-Object -InputObject {$_.MemoryTotalMB - $_.MemoryUsageMB} -Sum).Sum /1KB)}},
      @{N="TotalCPU";E={[Math]::Round(($esx | Measure-Object -Property CpuTotalMhz -Sum).Sum /1KB)}},
      @{N="TotalUsedCPU";E={[Math]::Round(($esx | Measure-Object -Property CpuUsageMhz -Sum).Sum /1KB)}},
      @{N="AvailableCPU";E={[Math]::Round(($esx | Measure-Object -InputObject {$_.CpuTotalMhz - $_.CpuUsageMhz} -Sum).Sum /1KB)}},
      @{N="TotalDiskSpace";E={[Math]::Round(($ds | where {$_.Type -eq "VMFS"} | Measure-Object -Property CapacityMB -Sum).Sum /1KB)}},
      @{N="ConfiguredDiskSpace";E={[Math]::Round(($ds | Measure-Object -InputObject {$_.CapacityMB - $_.FreeSpaceMB} -Sum).Sum /1KB)}},
      @{N="AvailableDiskSpace";E={[Math]::Round(($ds | Measure-Object -Property FreeSpaceMB -Sum).Sum /1KB)}},
      @{N="Total Configured Memory GB For Powered on VMs";E={[Math]::Round(($_ | Get-VM | Where-Object {$_.PowerState -eq "PoweredOn"} | %{$_.MemoryMB} | Measure-Object -Sum | Select -ExpandProperty Sum)/1KB) }}
  
  $cmd.commandtext = "INSERT INTO Clusterinfo (VCname) VALUES('{$val}')" -f $clusterinf.VCname
  $cmd.ExecuteNonQuery()   
  $val++     
  
  }
  $conn.close()
  </code></pre>
  
  <p>And this is working as intended i believe. Now my question is, how do I continue with the next column? So that I can add <code>DCname</code>, <code>Clustername</code>, <code>TotalPhysicalMemory</code> and so on?</p>
  
  <p>I have been trying with this insert statement: </p>
  
  <pre><code>$cmd.commandtext = "INSERT INTO Clusterinfo (VCname, DCname, CLustername) VALUES('$($clusterinf.VCname)','$($clusterinf.DCname)','$($clusterinf.Clustername)')"
  </code></pre>
  
  <p>But its not realy giving the wanted result, as it will insert every <code>VCname</code>, <code>DCname</code> and so on, into one row, and not into different rows.</p>
  
  <p>I have been looking at this question: <a href="https://stackoverflow.com/questions/20144768/powershell-how-to-query-multiple-classes-and-write-in-to-sql-table">Powershell How to query multiple classes and write in to SQL Table</a></p>
  
  <p>But I can't seem to get my head around a solution to my own question.</p>
  
  <p>Anybody got an idea on how to solve this?</p>