This Powershell function writes an object into a database table. If the table does not exist it will be created based on the properties of the object. For every property of the object a column will be created. SQL Server has been the primary target when writing this function, but some basic support has been added for other database engines.

More information about this script can be found on my blog here. GitHub repository here. You can also download the module from PowerShellGallery.com.

If you notice any problem feel free to open an issue on GitHub or give me a shout on twitter.  

Example usage:

PowerShell
Edit|Remove
Get-Process | Write-ObjectToSQL -Server localhost\sqlexpress -Database MyDB -TableName ProcessTable
If a table needs to be created the data type for each column will be converted from .Net data types into SQL Server data types.

Not all data types are supported. Unsupported data types will be ignored (but can be listed). If several objects are sent through the pipeline only the first object will be used for creating the template for the table.

Make sure that all objects in the pipeline have the exact same properties (this is usually the case). While creating the table the script will also add two default columns. One called "id" which is a regular auto counter (integer which increases with 1 for every row) and another column called "inserted_at" which will have a default value of GetDate() which represents the timestamp for when the row was inserted. If a property is named the same as one of these default columns then a "x" will be added before the name of those columns to avoid duplication. (if propertyname=id, then propertyname=xid, etc.)

Hashtables are handled slightly different. When using hashtables the script will simply use the keys as columns.

Keep in mind that properties on the objects are used. Some objects, like strings, might only have a length property but what you really want to insert into the table is the value of the string.

The following command would generate a table called myTable with one column called Length which would contain the length of the strings (probably not what you want):

PowerShell
Edit|Remove
'oink','meo' | Write-ObjectToSQL -Server localhost\sqlexpress -Database MyDB -TableName myTable
The following script is a better way to do it. Instead of piping the strings directly you could create custom objects. This will generate a table called AnimalSounds with one column called Animal and one collumn called Sound. After that the values will be inserted. 
PowerShell
Edit|Remove
$animal1 = New-Object -TypeName PSObject 
Add-Member -InputObject $animal1 -Type NoteProperty -Name 'Animal' -Value 'Pig' 
Add-Member -InputObject $animal1 -Type NoteProperty -Name 'Sound' -Value 'Oink' 
 
$animal2 = New-Object -TypeName PSObject 
Add-Member -InputObject $animal2 -Type NoteProperty -Name 'Animal' -Value 'Cat' 
Add-Member -InputObject $animal2 -Type NoteProperty -Name 'Sound' -Value 'Meo' 
 
$animal1$animal2 | Write-ObjectToSQL -Server localhost\sqlexpress -Database MyDB -TableName AnimalSounds
Or if you want to use hash tabes instead. This will generate a table called myTable with a column called 'text' which will contain the values 'oink' and 'meo':
PowerShell
Edit|Remove
@{'text'='oink'}, @{'text'='meo'| Write-ObjectToSQL -Server localhost\sqlexpress -Database MyDB -TableName myTable
Another thing to note is that this script will only take Property and NoteProperty into consideration.
So for example ScriptProperty and ParameterizedProperty will be ignored.
You can verify your objects with the Get-Member cmdlet and check the MemberType of each property.

Currently the script supports the following data types: