Instantly Stage Data Using PowerShell

Why?

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 Import-Csv cmdlet.

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!

1 Reply to “Instantly Stage Data Using PowerShell”

  1. Hey Tyler, thanks for sharing this blog. I was going through some stuff and it is really cool.

    This phrase is really catchy “BACKUP DATABASE [tempdb] TO DISK = ‘NUL’; ” Although it means nothing, it means so much 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *