Creates a DataTable based on an objects properties. Useful for converting any Powershell object into a DataTable which then can be bulk imported into a SQL Server table.
$dt = Get-PSDrive | Out-DataTable
This example creates a DataTable from the properties of Get-Alias and assigns output to $dt variable
This idea comes from Chad Miller's Out-DataTable: https://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd#content
After much discussion with Aaron Nelson and Chrissy LeMaire, and thanks to the enormous amount of support for this item, we’ve recognized that this is something we need to accomplish, one way or another.
We don’t want to offer an ETA on this as the work is not well understood by our team yet, and no one currently has immediate bandwidth on starting that investigation. But I want to stress the fact that is an important ask that we’re taking seriously as a priority.
In the meantime, it would be immensely useful if someone with expertise in the DataTable space could submit an RFC (basically a brief spec) to our PowerShell-RFC repository on GitHub. That way, we can have a discussion about what the design of a ConvertTo-DataTable cmdlet might look like before we dive in on an implementation. The process for doing so is located here: https://github.com/PowerShell/PowerShell-RFC/blob/master/RFC0000-RFC-Process.md#draft
I should also note that, given our desire to shrink the size of the PowerShell runtime over time, we believe that the development of this cmdlet/module should start on the PowerShell Gallery. However, this does not preclude us adding it to PowerShell 6.x as its quality improves and as others validate its usefulness.
I agree that the ConvertTo-DataTable name might work, I used the Out-DataTable name because that is already a popular command that the community put forward a long time ago, as well as the example.
That's a good point about ConvertTo-DataTable! I can see how that name may be more appropriate.
I think the ConvertTo-DataTable name would be appropriate. Out is to send it outside of the environment like to another device (host, printer, file). Here, we are converting the PSObject to be converted into a DataTable format.
Warren F. commented
Voted. Makes sense to me. Yes, SQL Server is a common use case, but as Chrissy and others have mentioned, this is a generic construct that many folks find handy for other scenarios, e.g.:
* In memory database-esque usage
* Integration with various database systems and other technologies that can work with datatables
Lastly - IMHO every new function added to PowerShell (with rare exceptions) should be included in a module, allowing folks to install / import it as needed. This case in particular, where the code could easily work down-level, should be included in a module that folks with older PowerShell versions could use. That said, with core functionality like this, even if it's a module, IMHO it would be better handled by the PowerShell team, or another Microsoft team (with the help from the OSS community, of course)
Please read below. We have already shown why this doesn't belong in the now defunct SQLPS module and should instead be available to every PowerShell user.
Joel "Jaykul" Bennett commented
Should probably be in the SQLPS module?
It is hard to imagine working in PowerShell and SQL Server without Out-DataTable. (or ConvertTo-Datatable) because the SqlBulkCopy Class, which eats datatables, is the obvious way of copying data into SQL Server.
Ben Miller commented
I use Out-DataTable every day and have automated processes that use it. I believe this is absolutely the right thing to do for the SQLPS side of things. When I am getting data out of one instance because SMO does not have access to all the DMVs, it would be great to not have to convert the results of rows into a DataTable for inserting into the stats instance.
Adding flavor to the conversation.... You can use ORMs like EntityFramework to work with database data in an object-y, scalable (depending on usage) way. I love doing this in PowerShell.
This makes me wonder how folks are using DataTable. Are people using it as a sort of ORM? Are they using it because of the query/sort/group semantics, something else?
I feel like if we understood the scenarios more it would be easier to scope this.
Martin Pugh commented
Just wondering if this shouldn't be called ConvertTo-DataTable (keeping in line with ConvertTo-CSV, etc).
data tables are okay but robots are huge
Aaron and I had talked about this and believe it's in the realm of the PowerShell team which is why we filed it here and not Connect.
DataTables are part of System.Data and aren't specific to SQL Server. I use DataTables when working with CSVs, SQLite and another guy used it with Postgres. It would be beneficial if it was a global out, like Out-GridView.
It will be cool when PowerShell gets to Linux, too, because then people could use it with MySQL ;)
Maximo Trinidad commented
In my opinion, this should be under SQL Server and not Windows Server. Now that we are trying to focus and spearheading the needed missing cmdlets in SQLPS module. This is a Good idea since MOW was the one who build the first function and was very use back in May 2006 (http://mow001.blogspot.com/2006/05/powershell-out-datagrid-update-and.html)
Lets talk about it
Scott Stauffer commented
I agree with Aaron and Chrissy's assertions. It should be all about adoption. After adoption, you will get more input. It's the classic chicken and egg thing... or wait, no... maybe it's the "putting the horse before the carriage" thing... Regardless, PowerShell is cool, Microsoft seems to want to use it for automation - probably a good choice. DBAs love automation and are wondering why they should use it if it only does a small bit of what they need without doing back flips. Increase adoption - where is MS Marketing when you need them! :) UserVoice is a great and Aaron and Chrissy's efforts to draw people into the conversation are equally great. I'd see value in "Out-DataTable" and it might make me take some time with PowerShell - "Make PowerShell Great Again!"
Joey, this would be more than cool, it would be extremely useful. This function was function was documented over 5 years ago but I will work on publicizing it more: https://blogs.technet.microsoft.com/heyscriptingguy/2010/11/01/use-powershell-to-collect-server-data-and-write-to-sql/
Thanks for responding. As you may be aware, getting the SQL Server community to adopt PowerShell has been challenging for a variety of reasons. Because of this, it will be hard to get proponents of Out-DataTable to jump in and vote because... we'll be trying to use Out-DataTable to recruit them.
Out-DataTable will be key for easy SQL imports. If we get it, we can also suggest to the SQL Server team a few fun ways to take advantage of it in SQLPS.
I understand your position, however, and will work to call in the cavalry.
It will also help PowerShell people more easily store their data in a SQL Server.
YES PLEASE! DataTables are amazing, and if they are made easily accessible, it will help the adoption of PowerShell within the SQL Server community.