Create a table with records on sql server with powershell: which is better?

Question

I need to create and populate a table on sql server db, starting with an array of powershell objects from select-object . I need to create a table with the correct data types and fields.

One way that I know well is export-csv and then invoke-sqlcmd with BULK INSERT , but I prefer to skip csv export.

How is invoke-sqlcmd usually used to create / populate a table, starting with an array of hash tables? Did you know that other methods do the same job without using invoke-sqlcmd ? Do I need to use System.Data ? How?

thanks

EDIT 1: Another possible way is New-Object System.Data.SqlClient.SqlConnection , for which I could ExecuteNonQuery() for each element of the array.

EDIT 2: Another option, always based on System.Data , updates the SqlDataAdapter instance.

Answer 1

I wrote a blog post for Scripting Guy that covers only such a scenario

http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/01/use-powershell-to-collect-server-data-and-write-to-sql.aspx

Using the code shown in the blog post, you can force any PowerShell command to create a System.DataTable, then create a SQL Server table, and finally insert a DataTable into the newly created table. Here is an example

 $dt = get-psdrive | out-datatable Add-SqlTable -ServerInstance "Z003\R2" -Database dbutility -TableName psdrive -DataTable $dt Write-DataTable -ServerInstance "Z003\R2" -Database "dbutility" -psdrive"diskspace" -Data $dt