Write-ObjectToSQL

1.13

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. 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
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. 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 with one column called Length which would contain the length of the strings (probably not what you want):
   
'oink','meo' | Write-ObjectToSQL -Server localhost\sqlexpress -Database MyDB -TableName myTable
   
The following command is a better way to do it. Instead of piping the strings directly you should create custom objects or, as in this example, hash tables. This will generate a table with a column called 'text' which will contain the values 'oink' and 'meo':

@{'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.

Currently the script supports the following data types:

Int32
UInt32
Int16
UInt16
Int64
UInt64
long
int
Decimal
Single
Double
Byte
SByte
String
DateTime
TimeSpan
datetime
string
bool
Boolean
GUID

Minimum PowerShell version

4.0

Installation Options

Copy and Paste the following command to install this package using PowerShellGet More Info

Install-Module -Name Write-ObjectToSQL

You can deploy this package directly to Azure Automation. Note that deploying packages with dependencies will deloy all the dependencies to Azure Automation. Learn More

Manually download the .nupkg file to your system's default download location. Note that the file won't be unpacked, and won't include any dependencies. Learn More

Author(s)

John Roos

Copyright

(c) 2017 John Roos. All rights reserved.

Owners

Tags

Database SQLServer Object

Cmdlets

Write-ObjectToSQL

Functions

Write-ObjectToSQL

Dependencies

This module has no dependencies.

Release Notes

Added logic to handle System.Nullable data types (thanks to beanska on GitHub for adding this)
Improved the GUID support to use uniqueidentifier when creating the table
Added the optional parameter PrimaryKey (thanks to lw-schick on GitHub for this idea)
When PrimaryKey is used, that column will be set to NOT NULL when creating the table
Added the SchemaName parameter to select which schema the table should have (thanks to lw-schick on GitHub for adding this)
Fixed a bug where zeroes were sometimes treated as null values (thanks to acheung456 in GitHub for reporting this)

Version History

Version Downloads Last updated
1.13 (current version) 8,687,789 2/11/2017
1.10 302 3/15/2016
1.8 146 8/3/2015