2

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.

Emiliano Poggi
  • 24,390
  • 8
  • 55
  • 67

1 Answers1

3

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
Chad Miller
  • 40,127
  • 3
  • 30
  • 34
  • Thanks, it looks very close to my requirements. I'll let you know. – Emiliano Poggi Apr 21 '11 at 04:52
  • Do I have to convert my array of hash tables into a datatable before? Is there some easy way to accomplish that, for example just using a `cast`? – Emiliano Poggi Apr 21 '11 at 05:05
  • Ok Chad! I've found on the internet the following post at [sqlservercentral](http://www.sqlservercentral.com/articles/powershell/65196/), not surprisingly written by you :), where you introduce the `out-datatable` function. Do you think it's a good choice? Does it keep data types? Thanks a lot for the help. – Emiliano Poggi Apr 21 '11 at 05:25
  • I consider the Scripting Guy article to be an updated version of teh SQLServerCentral article. PowerShell does a decent job of inferring datatypes, so I haven't messed with expliciting define the type. You would need to use getenumerator with hashtable. Here's an example of using out-datatable with hashtable: $a = @{name='john';dob='1/1/1980';age=31}; $a.GetEnumerator() | out-datatable – Chad Miller Apr 21 '11 at 12:32
  • new link for that blog: https://devblogs.microsoft.com/scripting/use-powershell-to-collect-server-data-and-write-to-sql/ – codeulike Feb 15 '23 at 17:07