Search code examples
sql-serverpowershellsystem.data

create table with records in sql server with powershell: what's best?

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

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

How invoke-sqlcmd is normally used to create/populate a table starting from array of hash tables? Do you know other ways doing the same job without using invoke-sqlcmd? Do I need to use System.Data? How to?

Thanks

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

EDIT 2: Another option, always based on System.Data, is update a SqlDataAdapter instance.

Solution

  • I wrote an blog post for the Scripting Guy that covers just 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 demonstrated in the blog post you can take any PowerShell command create a System.DataTable, then create a SQL Server Table and finally insert the DataTable into the newly created table. Here's 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