Storing Results From Powershell To Sql Table

I have an .ps1 script grabbing applications under each app pool as follows param([string]$appPool) import-module webadministration $apps = (Get-WebConfigurationProperty '/sy

Solution 1:

You can convert the result into a datatable and then load it into your database table using Data.SqlClient.SqlBulkCopy in PowerShell.

The script to convert your result into a datatable is already available open source: https://gallery.technet.microsoft.com/ScriptCenter/4208a159-a52e-4b99-83d4-8048468d29dd/

And this is an excellent article that can guide you into loading your data in a database table:

https://blogs.technet.microsoft.com/heyscriptingguy/2011/11/28/four-easy-ways-to-import-csv-files-to-sql-server-with-powershell/

You could do:

$appsDataTable = $applications | Out-DataTable

And then open a connection to SQL Server and do:

$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString$bulkCopy.DestinationTableName = $table$bulkCopy.BatchSize = $batchSize$bulkCopy.BulkCopyTimeout = $timeout$bulkCopy.WriteToServer($appsDataTable) 

Of course you will need to give values to the $connectionString, $table etc.