There are so many times I’ll receive a random CSV and be asked to compare something to a table in a database. Sometimes it’s just easier to work with these results in SQL Server. It’s pretty straight forward to perform this task using the “Import Flat File” option in SQL Server Management Studio (SSMS). It can also be performed pretty quickly using SSIS with an OLE DB Destination in a Data Flow Task. I’d like to demonstrate a much quicker way.
Thank you, dbatools.
The popular PowerShell module dbatools has the perfect solution for this. We can use the
Write-DbaDbTableData cmdlet to instantly perform this task. One of the most useful components of this cmdlet, in my opinion, is the “AutoCreateTable” switch parameter. If the table doesn’t exist, it will be created automatically. It’s important to note that the data types may not be optimal with this parameter, but usually we perform this type of task in a time-crunch.
Loading my services.
We can use the
Get-Service cmdlet in PowerShell to get a list of the services running on our machine. I’ll be taking the results from the cmdlet and loading them instantly into a table.
Write-DbaDataTable -SqlInstance localhost -Database Staging -Table Services -Schema dbo -InputObject $(Get-Service) -AutoCreateTable
That script will automatically create a table named “Services” (reserved word, I know) on my local instance. I can run the following query to get the results from my table:
USE Staging; GO SELECT [Name], RequiredServices, CanPauseAndContinue, CanShutdown, CanStop, DisplayName, DependentServices, MachineName, ServiceName, ServicesDependedOn, ServiceHandle, [Status], ServiceType, StartType, [Site], Container FROM dbo.[Services];
In my scenario, 310 rows were loaded with all sorts of various properties in which I can aggregate or filter on.
What about a CSV?
This would also work the same way, except the “-InputObject” parameter would reference an object typically imported using the
I really like having the ability to do this in a pinch. I’m sure many of you have encountered similar scenarios where this would need to be performed. I mentioned a few use cases for dbatools and PowerShell in a previous post, I think this is another great one!
Thanks for reading!